﻿import time
import zipfile
from urllib.parse import quote

from flask import Flask, render_template, request, redirect, jsonify, session, send_from_directory, flash, url_for, send_file
from flask_executor import Executor
from flask_login import login_required, current_user, LoginManager, login_user, logout_user
from sqlalchemy.exc import IntegrityError, SQLAlchemyError
from sqlalchemy.orm import sessionmaker
from docx import Document
from docx.shared import Inches, Pt
from docx.enum.text import WD_ALIGN_PARAGRAPH
from logger_config import configure_logging  # 导入日志配置函数
from flask_migrate import Migrate
from werkzeug.security import generate_password_hash, check_password_hash
from flask_limiter import Limiter
from flask_limiter.util import get_remote_address

import os
# import random
import secrets
from sqlalchemy.testing.pickleable import User
from sqlalchemy import text, over, func, cast, Integer, or_, and_, event, Engine, create_engine
from waitress import serve
import click
from flask.cli import with_appcontext

import CommonUtils
from exts import db, run_plant_uml, update_uml_picture
from datetime import datetime
from sqlalchemy import inspect, MetaData, Table
from toolbox import save_file_with_timestamp, check_data_flow, merge_string, format_data_flow, validate_user_data
from model import UserTable, CustomerTable, ProjectTable, EcuTable, PermissionTable, SecurityAttrTable, \
    EcuFeatureTable, FeatureMapSecurityAttrTable, EcuMapFeatureTable, LogTable, ThreadTypeTable, \
    ThreadAssessmentParaTable, RiskAssessmentParaTable, ItemTable, FuncRelationTable, RiskAssessmentEcuTable, \
    generate_view_sql, get_all_features, RiskTreatmentStrategyTable, ThreatAnalysisTable, \
    create_risk_assessment_ecu_table, create_threat_analysis_table, create_func_relation_table, \
    create_ecu_feature_table, create_feature_map_security_attr_table, create_thread_assessment_para_table, \
    create_risk_assessment_para_table, create_ecu_table, import_cybersecurity_req_table, CybersecurityReqTable, \
    auto_calc_attack_level, auto_calc_risk_level, auto_ai_process_1_json, export_safety_req_by_ecu, initdb, \
    test_internet, AssetScenarioDescTable, AssetRiskScenarioAssessmentTable, \
    create_ecu_table_new, user_types, update_cache_risk_assessment, update_cache_threat_analysis, \
    auto_calc_r_risk_level, clear_tara_table, auto_process_threat_analysis, auto_process_risk_assessment, above2, \
    get_tara_data_from_db, copy_tara_table_to_share, ThreatAnalysisShareTable, RiskAssessmentShareTable, \
    sync_all_tara_table, duplicate_req_to_project, update_item_table, export_all_reqs, export_tara_table_to_excel, \
    is_match_cal_req, save_all_reqs_to_dict, save_ecu_reqs_to_dict, auto_plant_uml, export_ecu_reqs, \
    create_ecu_map_feature_table, del_ecu_map_feature_table, create_item_table_uml, export_tara_report_to_word, \
    auto_process_item_desc, export_all_reqs_to_word, sync_item_table_plantuml, import_tara_table, \
    import_item_impact_table, get_impact_level, import_tara_rule_mapping_table, read_impact_mapping_matrix_from_csv, \
    get_risk_level_desc
import shutil

app = Flask(__name__)

# 设置默认配置
# app.default_config['EXECUTOR_PUSH_APP_CONTEXT'] = True
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///../database/data.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SECRET_KEY'] = secrets.token_hex(16)  # 设置一个密钥用于session加密
# 添加 Flask-Executor 配置
app.config['EXECUTOR_PUSH_APP_CONTEXT'] = True  # 确保任务执行时推送应用上下文

db.init_app(app)
executor = Executor(app)  # 初始化 Executor

def init_db_settings(app):
    with app.app_context():
        @event.listens_for(db.engine, "connect")
        def set_sqlite_timeout(dbapi_connection, connection_record):
            if hasattr(dbapi_connection, 'cursor'):
                cursor = dbapi_connection.cursor()
                try:
                    cursor.execute("PRAGMA busy_timeout = 10000")
                    cursor.close()
                except Exception as e:
                    app.logger.error(f"设置数据库超时时间失败: {str(e)}")
                    if not cursor.closed:
                        cursor.close()


init_db_settings(app)  # 添加这行

# Flask-Login setup  待补充完整
login_manager = LoginManager()
login_manager.init_app(app)
login_manager.login_view = 'to_login'

@login_manager.user_loader
def load_user(user_id):
    return UserTable.query.get(int(user_id))

# limiter = Limiter(
#     get_remote_address,  # 用于限制的键（通常是客户端 IP 地址）
#     app=app,             # Flask 应用实例
#     default_limits=["200 per day", "50 per hour"]  # 默认限制规则
# )

"""
    flask db migrate -m "FeatureMapSecurityAttrTable add req_name"
    flask db upgrade    ## 执行升级
    flask db downgrade  ## 回滚
"""
migrate = Migrate(app, db)


app_name = "车辆TARA智能管理平台"
# 导出目录
EXPORT_DIR = 'export'
if not os.path.exists(EXPORT_DIR):
    os.makedirs(EXPORT_DIR)

# 配置日志
"""
app.logger.info('访问了首页')
app.logger.error('发生了一个错误')
    DEBUG: 详细的调试信息，通常用于开发阶段。#
    INFO: 确认程序按预期运行。#
    WARNING: 表示一些不期望的事情发生，但程序仍然正常运行。#
    ERROR: 由于更严重的问题，程序的某些功能无法正常工作。#
    CRITICAL: 严重的错误，可能导致程序无法继续运行。
"""
configure_logging(app)

read_impact_mapping_matrix_from_csv()

# 用户加载回调
@login_manager.user_loader
def load_user(user_id):
    return UserTable.query.get(int(user_id))

# 在应用启动时打印系统信息
# print_system_info()

# 定义上下文处理器
@app.context_processor
def inject_strings():
    strings = {
        'hello': 'Hello',
        'welcome': 'Welcome to our site',
        'title': '定义上下文处理器 Demo'
    }
    return dict(strings=strings)

def delete_table_data(model_class):
    # 获取类对应的表名
    table_name = model_class.__tablename__

    # 获取数据库的元数据
    metadata = MetaData(bind=db.engine)
    metadata.reflect()

    # 获取指定表的Table对象
    table = Table(table_name, metadata, autoload_with=db.engine)

    # 删除表中的所有数据
    db.session.execute(table.delete())
    db.session.commit()

# 使用视图
def query_map_view():
    with app.app_context():
        create_map_view()
        # 获取所有 feature
        features = get_all_features()
        # 生成列名列表，并确保每个列名都用双引号括起来
        columns = ['"ecu_abbr"'] + [f'"{feature}"' for feature in features]
        # 生成 SQL 查询字符串
        # query_str = "SELECT " + ", ".join(columns) + " FROM ecu_map_feature_view"
        query_str = "SELECT *  FROM ecu_map_feature_view"
        # 执行查询
        result = db.session.execute(text(query_str)).fetchall()
        print(columns)
        for row in result:
            print(row)


# 创建视图
def create_map_view():
    with app.app_context():
        # 检查视图是否存在，如果存在则删除
        drop_view_sql = "DROP VIEW IF EXISTS ecu_map_feature_view"
        with db.engine.connect() as connection:
            connection.execute(text(drop_view_sql))

        # 生成并执行创建视图的 SQL 语句
        view_sql = generate_view_sql()
        with db.engine.connect() as connection:
            connection.execute(text(view_sql))

@app.route('/')
@app.route('/index')
@login_required
def index():
    # 直接使用 current_user 获取当前登录用户
    user = current_user

    # 获取项目名称
    project_name = user.project.name if user.project else "-"

    # 打印日志
    print(f'登录成功: {project_name}, {user.name}, {app_name}')

    # 渲染模板并传递数据
    return render_template("index.html",
                           user_name=user.name,
                           projectName=project_name,
                           app_name=app_name,
                           role=user.role)

@app.route('/login')
# @limiter.limit("5 per minute")  # 每分钟最多 5 次登录尝试
def login():
    text = ['1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F', 'H', 'J', 'K', 'L', 'M', 'N',
            'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
    verify_code = CommonUtils.getByLengthRomdom(text, 4)
    base64_str = CommonUtils.getCaptchaImageBase64ByStr(verify_code)

    return render_template("login.html", base64_str=base64_str, verifyCode=verify_code)


@app.route('/getEcuMapFeatureTable', methods=['GET'])
@login_required
def get_ecu_map_feature_table():
    page = request.args.get('page', default=1, type=int)
    limit = request.args.get('limit', default=20, type=int)
    pagination = EcuTable.query.filter_by(Project_id=current_user.project_id).paginate(page=page, per_page=limit,
                                                                                        error_out=False)
    ecu_list = []

    for ecu in pagination.items:
        ecu_list.append({
            'id': ecu.id,
            'name': ecu.name,
            'abbr': ecu.abbr_ext,
            'pcb': ecu.pcb,
            'chipset': ecu.chipset,  # 芯片
            'Bootloader': ecu.Bootloader,  # 引导加载程序
            'OS': ecu.OS,
            # 'Linux': ecu.Linux,  # Linux系统
            # 'Windows': ecu.Windows,  # Windows系统
            # 'Andriod': ecu.Andriod,  # Android系统
            # 'QNX': ecu.QNX,  # QNX系统
            # 'HarmonyOS': ecu.HarmonyOS,  # 鸿蒙系统
            # 'RTOS': ecu.RTOS,  # 实时操作系统
            'app': ecu.app,  # 应用软件
            'app_3rd': ecu.app_3rd,  # 第三方应用
            'data_1': ecu.data_1,  # 重要数据
            'data_2': ecu.data_2,  # 个人敏感数据
            'data_3': ecu.data_3,  # 关键数据
            'data_key': ecu.data_key,  #
            'data_car': ecu.data_car_info,  #
            'data_log': ecu.data_log,  #
            'interface_usb': ecu.interface_usb,  # USB接口
            'interface_sd': ecu.interface_sd,  # SD卡接口
            'interface_debug': ecu.interface_debug,  # 调试接口
            'can_bus': ecu.can_bus,  # CAN通信
            'eth_bus': ecu.eth_bus,  # 以太通信
            'wireless_bt': ecu.wireless_bt,  # 蓝牙
            'wireless_wifi': ecu.wireless_wifi,  # WIFI
            'wireless_rf': ecu.wireless_rf,  # 射频通信
            'wireless_mobile': ecu.wireless_mobile,  # 蜂窝通信
            'wireless_gnss': ecu.wireless_gnss,  # GNSS
            'wireless_v2x': ecu.wireless_v2x,  # V2X
            'ota_upgrade': ecu.ota_upgrade,  # OTA升级
            'usb_upgrade': ecu.usb_upgrade,  # USB升级
            'obd_upgrade': ecu.obd_upgrade,  # OBD升级
            'obd_debug': ecu.obd_debug,  # OBD诊断
            'remote_debug': ecu.remote_debug,  #
            'idps': ecu.intrusion_detection_prevention_systems, # IDPS
            'cal_level': ecu.cal_level
        })
    # print(ecu_list)
    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': pagination.total,
        'data': ecu_list
    })

@app.route('/getEcuFeatureView', methods=['GET'])
@login_required
def get_ecu_feature_view():
    # 直接执行 SQL 查询
    sql = text("SELECT * FROM ecu_map_feature_view")
    rows = db.session.execute(sql).fetchall()

    features = get_all_features()  # 获取所有 feature

    # 将查询结果转换为列表
    data = []
    row_dict = []
    for row in rows:
        row_dict = {
            'ecu_abbr': row.ecu_abbr
        }
        # 动态添加所有 feature 列
        for feature in features:
            row_dict[feature] = getattr(row, feature, None)
        data.append(row_dict)

    print(f"row_dict: {row_dict}")

    # 返回 JSON 格式的数据
    # return jsonify(data)
    print(f'get_ecu_feature_view -> {data}')
    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': len(data),
        'data': data
    })

@app.route('/get_items', methods=['GET'])
@login_required
def get_items():
    items = ItemTable.query.all()
    data = [{'id': item.id, 'name': item.name} for item in items]
    return jsonify(data)
@app.route('/getItemTable')
@login_required
def get_item_table():
    page = request.args.get('page', default=1, type=int)
    limit = request.args.get('limit', default=20, type=int)

    # items = ItemTable.query.filter_by(project_id=current_user.project_id)
    # 查询当前用户项目下的所有ItemTable数据，并进行分页处理
    pagination = ItemTable.query.filter_by(project_id=current_user.project_id).paginate(page=page, per_page=limit,
                                                                                        error_out=False)
    # items = pagination.items
    print(f'getItemTable: page={page}, limit={limit}, pagination={pagination}')
    uml_path = f'static/plantuml/{current_user.project_id}'
    item_list = []
    pid = current_user.project_id
    for item in pagination.items:
        uml_pic = f'item_{item.id}_uml.png'
        target_file = os.path.join(uml_path, uml_pic)
        if not os.path.exists(target_file):
            print(f'UML file not found: {target_file}')
            target_file = None

        item_list.append({
            'id': item.id,
            'name': item.name,
            'number': item.number,
            'desc': item.desc,
            'system': item.system,
            'data_flow': item.data_flow,
            'ecu_list': item.ecu_list,
            'plant_uml': item.plant_uml,
            'file': target_file,

            's1': item.safety_impact_level,
            's2': item.safety_impact_desc,
            'f1': item.economic_impact_level,
            'f2': item.economic_impact_desc,
            'o1': item.operation_impact_level,
            'o2': item.operation_impact_desc,
            'p1': item.privacy_impact_level,
            'p2': item.privacy_impact_desc,

            'project_name': item.project.name
        })

    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': pagination.total,
        'data': item_list
    })

@app.route('/addItem', methods=['POST'])
@login_required
def add_item():
    data = request.form
    new_item = ItemTable(
        name=data['name'],
        number=data['number'],
        desc=data['desc'],
        ecu_list=data['ecu_list'],
        project_id=current_user.project_id
    )
    db.session.add(new_item)
    db.session.commit()
    return jsonify({
        'code': 0,
        'msg': '功能域添加成功'
    })
@app.route('/updateItemUML', methods=['POST'])
def update_item_uml():
    data = request.json
    item_id = data['id']
    field = data['field']
    value = data['value']
    print(f'updateItem: {item_id}, {field}, {value}')

    item = ItemTable.query.get(item_id)
    if item:
        setattr(item, field, value)
        db.session.commit()
        if value and '[' in value:
            update_uml_picture(value, item.name, item_id, current_user.project_id)
        return jsonify({'msg': '更新成功'})
    else:
        return jsonify({'msg': '更新失败'}), 400
@app.route('/editItem', methods=['POST'])
@login_required
def edit_item():
    data = request.form
    item = ItemTable.query.get(data['id'])
    if item:
        item.name = data['name']
        item.number = data['number']
        item.desc = data['desc']
        item.ecu_list = data['ecu_list']
        # item.project_id = data['project_id']
        db.session.commit()
        return jsonify({
            'code': 0,
            'msg': '功能域编辑成功'
        })
    else:
        return jsonify({
            'code': 1,
            'msg': '功能域不存在'
        })
@app.route('/deleteItem/<int:id>', methods=['DELETE'])
@login_required
def delete_item(id):
    item = ItemTable.query.get(id)
    print(f'delete_item: {id}')
    if item:
        db.session.delete(item)
        db.session.commit()
        return jsonify({
            'code': 0,
            'msg': '功能域删除成功'
        })
    else:
        return jsonify({
            'code': 1,
            'msg': '功能域不存在'
        })
@app.route('/addItemForm')
@login_required
def add_item_form():
    return render_template('AssetIdentification/addItemForm.html')
@app.route('/editItemForm')
@login_required
def edit_item_form():
    id = request.args.get('id')
    item = ItemTable.query.get(id)
    return render_template('AssetIdentification/editItemForm.html', item=item)
@app.route('/funcDomain')
@login_required
def func_domain():
    return render_template('ItemDefinition/item_management.html')
@app.route('/security_reqs_alloc')
@login_required
def security_reqs_alloc():
    # return render_template('Report/security_reqs_alloc.html')
    return render_template('report/security_reqs_alloc.html')
@app.route('/ecuFeatures')
@login_required
def ecuFeatures():
    return render_template('AssetIdentification/ecu_features_management.html')

@app.route('/safetyAsset')
@login_required
def safetyAsset():
    return render_template('AssetIdentification/safty_asset_management.html')

@app.route('/damageScenario')
@login_required
def damage_scenario():
    return render_template('tara/damage_scenario_management.html')
@app.route('/risk_level_form')
@login_required
def risk_level_form():
    return render_template('tara/risk_level_management.html')

@app.route('/threatScenario')
@login_required
def threat_scenario():
    return render_template('tara/threat_scenario_management.html')


@app.route('/riskDecision')
@login_required
def riskDecision():
    return render_template('tara/risk_decision_management.html')
@app.route('/risk_disposal')
@login_required
def risk_disposal():
    return render_template('tara/risk_disposal_form.html')
@app.route('/risk_disposal2')
@login_required
def risk_disposal2():
    return render_template('RiskAssessment/risk_disposal_form2.html')
@app.route('/safety_req_form')
@login_required
def safety_req_form():
    return render_template('BaseData/safety_req_management.html')
@app.route('/experience_repository_form')
@login_required
def experience_repository_form():
    return render_template('BaseData/experienceRepositoryForm.html')

@app.route('/reportManage')
@login_required
def reportManage():
    # flash("闪现消息...")
    return render_template('report/report_management.html')


@app.route('/customerManage')
@login_required
def customerManage():
    flash("闪现消息...")
    return render_template('Setup/customer_management.html')
@app.route('/logs', methods=['GET'])
def get_logs():
    LOG_FILE = 'app.log'
    try:
        # 检查日志文件是否存在
        if not os.path.exists(LOG_FILE):
            return jsonify({"code": 404, "msg": "Log file not found", "data": []})

        # 读取日志文件的最后 100 行
        with open(LOG_FILE, 'r') as file:
            lines = file.readlines()[-300:]  # 获取最后 300 行

        # 返回日志内容
        return jsonify({
            "code": 0,
            "msg": "success",
            "data": lines
        })
    except Exception as e:
        return jsonify({"code": 500, "msg": str(e), "data": []})

@app.route('/logManage')
@login_required
def logManage():
    return render_template('Setup/log_management.html')

@app.route('/features_form')
@login_required
def features_form():
    return render_template('BaseData/ecu_features_management.html')

@app.route('/show_tara_report')
@login_required
def show_tara_report():
    return render_template('report/tara_report.html')


@app.route('/addFunctionForm')
@login_required
def add_function_form():
    return render_template('ItemDefinition/addFunctionForm.html')

@app.route('/features_safety_attr_form')
@login_required
def features_safety_attr_form():
    return render_template('BaseData/ecu_features_management.html')

@app.route('/threat_analysis_para_form')
@login_required
def threat_analysis_para_form():
    return render_template('BaseData/ecu_features_management.html')

@app.route('/risk_assessment_para_form')
@login_required
def risk_assessment_para_form():
    return render_template('BaseData/ecu_features_management.html')

@app.route('/dbManage')
@login_required
def dbManage():
    # print(f'login_User: {session.get('user_name')}')
    # 查询视图
    return render_template('Setup/db_management.html')


@app.route('/dbView')
@login_required
def dbView():
    # 获取所有表名
    inspector = inspect(db.engine)
    table_names = inspector.get_table_names()

    # 读取每个表的数据
    tables = {}
    for table_name in table_names:
        # 将表名转换为模型类名
        model_name = ''.join(word.capitalize() for word in table_name.split('_'))
        model = globals().get(model_name)

        # print(f'{table_name} -> {model_name} -> {model}')
        if model:
            columns = [column.name for column in model.__table__.columns]
            total_count = db.session.query(model).count()
            if total_count >200:
                row_1 =db.session.query(model).limit(50).all()
                row_2 =db.session.query(model).offset( total_count - 50).limit(50).all()
                rows = row_1 + row_2
            else:
                rows = db.session.query(model).all()

            rows = [
                [getattr(row, column) for column in columns]
                for row in rows
            ]
            tables[table_name] = {'columns': columns, 'rows': rows}
        else:
            print(f'ERROR: {table_name} -> None')

    # print(f'dbView(): {tables}')
    return render_template('Setup/db_view.html', table_names=table_names, tables=tables)


@app.route('/getCaptchaImage')
def getCaptchaImage():
    text = ['1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F', 'H', 'J', 'K', 'L', 'M', 'N',
            'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
    verifyCode = CommonUtils.getByLengthRomdom(text, 4)
    base64_str = CommonUtils.getCaptchaImageBase64ByStr(verifyCode)
    return base64_str


@app.route('/logout', methods=['post'])
@login_required
def logout():
    app.logger.info(f'logout user: {session.get("user_name")}')

    logout_user()
    # flash('您已注销！', 'info')
    print(f'logout user: OK')
    # session.pop('user_id', None)
    # session.pop('user_name', None)
    # return redirect(url_for('index'))
    return {"code": 200, "data": None, "msg": "退出成功"}

@app.route('/welcome')
def welcome():
    return render_template("mainPage.html")
    # return render_template("index/welcome.html")



@app.route('/get_security_reqs_alloc', methods=['GET'])
@login_required
def get_security_reqs_alloc():

    # 获取过滤条件
    ecu = request.args.get('select_ecu')
    # print(f'get_security_reqs_alloc::: ecu: {ecu}')
    if ecu is None:
        return jsonify({
            'code': 1,
            'msg': '没有选择ECU',
            'count': 0,
            'data': ''
        })
    ecu_record = EcuTable.query.get(int(ecu))
    result = save_ecu_reqs_to_dict(current_user.project_id, ecu_record)

    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': len(result),
        'data': result
    })

@app.route('/getEcuDefine')
@login_required
def get_ecu_define():
    return render_template("ItemDefinition/ecu_define_new.html")


@app.route('/getFuncRelation')
@login_required
def getFuncRelation():
    return render_template("ItemDefinition/func_relation.html")

@app.route('/get_filter_items', methods=['GET'])
@login_required
def get_filter_items():
    items = ItemTable.query.filter_by(project_id=current_user.project_id).all()
    return jsonify([{'id': item.id, 'name': item.name} for item in items])

@app.route('/get_filter_ecu', methods=['GET'])
@login_required
def get_filter_ecu():
    items = EcuTable.query.filter_by(Project_id=current_user.project_id).all()
    return jsonify([{'id': item.id, 'name': item.abbr} for item in items])

@app.route('/get_filter_features', methods=['GET'])
@login_required
def get_filter_features():
    items = EcuFeatureTable.query.all()
    return jsonify([{'id': item.id, 'name': item.feature} for item in items])

@app.route('/userManage')
@login_required
def user_manage():
    return render_template("Setup/user_management.html")

@app.route('/setup')
@login_required
def setup():
    return render_template("index/system.html")

@app.route('/tologin', methods=['post','GET'])
def to_login():
    if request.method == 'POST':
        username = request.form.get("username")
        password = request.form.get("password")
        remember = True if request.form.get('remember') else False  # 检查是否勾选"记住我"
        # app.logger.debug(f'User login: {username}, {password}, {remember}')
        result_ok = {'code': 200, 'data': None, 'msg': '登录成功'}
        result = {'code': 201, 'data': None, 'msg': '用户名或密码错误！'}
        # username = 'TestUser'
        # password = '0000'
        # hashed_password = generate_password_hash(password)
        user = UserTable.query.filter_by(name=username).first()
        #if user and check_password_hash(user.pwd, password):
        if user:
            if user.state == 1:  # 检查账号状态
                app.logger.info(f'user login success: {user.name} ({user.uid})')

                login_user(user, remember=True)  # 启用"记住我"功能
                user.last_login = datetime.now().strftime('%Y-%m-%d %H:%M:%S')  # 更新最后登录时间
                db.session.commit()

                # session['user_id'] = user.uid
                # session['user_name'] = user.name  # 存储用户到session中
                # session['user_role'] = user.role
                # session['CustomerID'] = user.customer_id
                # current_user.project_id = user.project_id

                flash('登录成功！', 'success')
                return result_ok
            else:
                flash('账号已停用！', 'error')
                result['msg'] = "账号已停用"
                app.logger.warning(f'user login failed (账号已停用): {user.name} ({user.uid})')
                return result
        else:
            flash('用户名或密码错误！', 'error')
            app.logger.warning(f'user login failed: <{username}> ')
            return result
    # 如果是 GET 请求，重定向到 login
    return redirect(url_for('login'))

@app.route('/getUsers')
@login_required
def get_users():
    users = UserTable.query.all()
    user_list = []

    for user in users:
        customer = CustomerTable.query.filter_by(id=user.customer_id).first()
        customer_name = customer.name if customer else ' - '
        if user.state == 1:
            sta = '启用'
        else:
            sta = '停用'
        user_list.append({
            'uid': user.uid,
            'name': user.name,
            'role': user_types[int(user.role)],
            # 'role': user.role,
            'customer_name': customer_name,
            'state': sta,
            'phone': user.phone,
            'email': user.email,
            'remark': user.remark
        })

    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': len(user_list),
        'data': user_list
    })

@app.route('/getCustomers')
@login_required
def get_customers():
    customers = CustomerTable.query.all()
    customer_list = []

    for customer in customers:
        customer_list.append({
            'id': customer.id,
            'name': customer.name,
            'name_en': customer.name_en,
            'remark': customer.remark,
            'create_time': customer.create_time
        })

    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': len(customer_list),
        'data': customer_list
    })

@app.route('/addUserForm')
@login_required
def add_user_form():
    return render_template('Setup/addUserForm.html')

@app.route('/addEcuForm')
@login_required
def add_ecu_form():
    return render_template('ItemDefinition/addEcuForm.html')

@app.route('/editEcuForm')
@login_required
def edit_ecu_form():
    ecu_id = request.args.get('id')
    ecu = db.session.get(EcuTable, int(ecu_id))
    print(f'editEcuForm: {ecu_id}, {ecu}')
    return render_template('ItemDefinition/editEcuForm.html', id=ecu_id, ecu=ecu)

@app.route('/editUserForm')
@login_required
def edit_user_form():
    uid = request.args.get('uid')
    user = db.session.get(UserTable, int(uid))
    print(f'editUserForm: {uid}, {user}')
    return render_template('Setup/editUserForm.html', user=user)


@app.route('/checkUsername', methods=['POST'])
@login_required
def check_username():
    name = request.form.get('name')
    exists = UserTable.query.filter_by(name=name).first() is not None
    return jsonify({'exists': exists})


@app.route('/addUser', methods=['POST'])
@login_required
def add_user():
    data = request.form
    print(data)
    error = validate_user_data(data)
    if error:
        return jsonify({'code': 1, 'msg': error}), 400

    try:
        new_user = UserTable(
            name=data.get('name'),
            pwd=generate_password_hash(data['pwd']),
            role=data['role'],
            customer_id=data['customer_id'],
            phone=data['phone'],
            email=data['email'],
            remark=data['remark'],
            state=1
        )
        # print(f'new user pwd: {data['pwd']} -- {new_user.pwd}')
        db.session.add(new_user)
        db.session.commit()
        # logging.debug(f"用户 {data.get('name')} 添加成功")
        return jsonify({'code': 0, 'msg': '用户添加成功'})
    except IntegrityError:
        db.session.rollback()
        return jsonify({'code': 1, 'msg': '用户名已存在'}), 400
    except Exception as e:
        db.session.rollback()
        # logging.error(f"添加用户失败: {str(e)}")
        return jsonify({'code': 1, 'msg': '服务器内部错误'}), 500



@app.route('/editUser', methods=['POST'])
@login_required
def edit_user():
    data = request.form
    print(f'editUser: {data}')
    user = db.session.get(UserTable, data['uid'])
    if user:
        user.name = data['name']
        # user.pwd = data['pwd']
        user.role = data['role']
        user.customer_id = data['customer_id']
        user.phone = data['phone']
        user.email = data['email']
        user.remark = data['remark']
        db.session.commit()
        return jsonify({
            'code': 0,
            'msg': '用户编辑成功'
        })
    else:
        return jsonify({
            'code': 1,
            'msg': '用户不存在'
        })

@app.route('/addCustomerForm')
@login_required
def add_customer_form():
    return render_template('Setup/addCustomerForm.html')
@app.route('/editCustomerForm/<id>')
@login_required
def edit_customer_form(id):
    print(f'editCustomerForm: {id}')
    customer_id = id
    customer = db.session.get(CustomerTable, int(customer_id))

    return render_template('Setup/editCustomerForm.html', customer=customer)

@app.route('/checkCustomerName', methods=['POST'])
@login_required
def check_customer_name():
    name = request.form.get('name')
    exists = CustomerTable.query.filter_by(name=name).first() is not None
    return jsonify({'exists': exists})

@app.route('/addCustomer', methods=['POST'])
@login_required
def add_customer():
    try:
        data = request.form
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        # 验证必填字段
        if not data.get('name') or not data.get('name_en'):
            return jsonify({
                'code': 1,
                'msg': '企业名称和英文名称为必填项'
            })

        # 检查企业名称是否已存在
        if CustomerTable.query.filter_by(name=data['name']).first():
            return jsonify({
                'code': 1,
                'msg': '企业名称已存在'
            })

        # 创建新企业
        new_customer = CustomerTable(
            name=data['name'],
            name_en=data['name_en'],
            remark=data.get('remark', ''),  # 如果没有提供备注，使用空字符串
            create_time=current_time,
            update_time=current_time
        )

        db.session.add(new_customer)
        db.session.commit()

        return jsonify({
            'code': 0,
            'msg': '企业添加成功'
        })

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"添加企业失败: {str(e)}")
        return jsonify({
            'code': 1,
            'msg': f'添加企业失败: {str(e)}'
        })

@app.route('/updateCustomer', methods=['POST'])
@login_required
def update_customer():
    try:
        data = request.form
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        print(f'updateCustomer: {data}')
        # 验证必填字段
        if not data.get('id'):
            return jsonify({
                'code': 1,
                'msg': '系统异常，请联系管理员处理'
            })
        if not data.get('name') or not data.get('name_en'):
            return jsonify({
                'code': 1,
                'msg': '企业名称和英文名称为必填项'
            })

        # 根据id找到对应的表项
        customer_id = data.get('id')
        item = CustomerTable.query.filter_by(id=customer_id).first()
        if not item:
            return jsonify({
                'code': 1,
                'msg': '数据库异常，请联系管理员处理'
            })
        item.name = data['name']
        item.name_en = data['name_en']
        item.remark = data.get('remark', '')
        item.update_time = current_time
        db.session.commit()

        return jsonify({
            'code': 0,
            'msg': '企业信息修改成功'
        })

    except Exception as e:
        db.session.rollback()
        app.logger.error(f"企业信息修改失败: {str(e)}")
        return jsonify({
            'code': 1,
            'msg': f'企业信息修改失败: {str(e)}'
        })


@app.route('/deleteEcu/<int:ecu_id>', methods=['DELETE'])
@login_required
def delete_ecu(ecu_id):
    ecu = db.session.get(EcuTable, ecu_id)
    if ecu:
        db.session.delete(ecu)
        db.session.commit()
        return jsonify({
            'code': 0,
            'msg': '删除成功'
        })
    else:
        return jsonify({
            'code': 1,
            'msg': '表项不存在'
        })

@app.route('/deleteUser/<int:uid>', methods=['DELETE'])
@login_required
def delete_user(uid):
    user = db.session.get(UserTable, uid)
    if user:
        db.session.delete(user)
        db.session.commit()
        return jsonify({
            'code': 0,
            'msg': '用户删除成功'
        })
    else:
        return jsonify({
            'code': 1,
            'msg': '用户不存在'
        })


@app.route('/getThreatAnalysis', methods=['GET'])
@login_required
def get_threat_analysis():
    page = request.args.get('page', default=1, type=int)
    limit = request.args.get('limit', default=10, type=int)
    ecu = request.args.get('ecu')
    pid = current_user.project_id
    # threat_analysis_query = ThreatAnalysisTable.query.filter_by(project_id=current_user.project_id).all()
    print(f'get_threat_analysis:{pid} {page}, {limit}, {ecu}')
    filter_ecu = request.args.get('filter_ecu')
    filter_feature = request.args.get('filter_feature')
    filter_security = request.args.get('filter_security')
    filter_attack_level = request.args.get('filter_attack_level')
    print(f'filter_ecu: {filter_ecu}, filter_feature: {filter_feature}')

    threat_analysis_query = ThreatAnalysisTable.query.filter_by(project_id=pid)
    if filter_attack_level:
        threat_analysis_query = threat_analysis_query.filter(ThreatAnalysisTable.attack_level.like(f'%{filter_attack_level}%'))
    if filter_ecu:
        threat_analysis_query = threat_analysis_query.join(EcuTable).filter(EcuTable.abbr.like(f'%{filter_ecu}%'))
    if filter_feature:
        threat_analysis_query = threat_analysis_query.join(EcuFeatureTable).filter(EcuFeatureTable.feature.like(f'%{filter_feature}%'))
    if filter_security:
        threat_analysis_query = threat_analysis_query.join(SecurityAttrTable).filter(SecurityAttrTable.name.like(f'%{filter_security}%'))

    paginated_result = threat_analysis_query.paginate(page=page, per_page=limit, error_out=False)
    # if ecu:
    #     paginated_result = threat_analysis_query.filter_by(ecu=ecu, project_id=pid).paginate(page=page, per_page=limit, error_out=False)
    # else:
    #     paginated_result = threat_analysis_query.filter_by(project_id=pid).paginate(page=page, per_page=limit, error_out=False)

    result = []
    # print(f'paginated_result.items count: {paginated_result.items}')
    for threat_analysis in paginated_result.items:
        result.append({
            'id': threat_analysis.id,
            'ecu': threat_analysis.ecu.abbr,
            'ecu_feature': threat_analysis.ecu_feature.feature,
            'security_attr': threat_analysis.security_attr.name,
            'thread_type': threat_analysis.thread_type.name,
            'attack_path_number': threat_analysis.attack_path_number,
            'attack_path': threat_analysis.attack_path,
            'thread_scenario_number': threat_analysis.thread_scenario_number,
            'thread_scenario': threat_analysis.thread_scenario,
            'vdt_id': threat_analysis.vdt_id,
            'asl_id': threat_analysis.asl_id,
            'knowledge_level_id': threat_analysis.knowledge_level_id,
            'woo_id': threat_analysis.woo_id,
            'device_id': threat_analysis.device_id,
            'attack_value': threat_analysis.attack_value,
            'attack_level': threat_analysis.attack_level,
            # 'project_id': threat_analysis.project_id
        })

    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': paginated_result.total,
        'data': result
    })
@app.route('/getSumData', methods=['GET'])
@login_required
def get_sum_data():
    # 生成攻击等级数据
    pid = current_user.project_id
    # print(f'get_sum_data:{pid}')
    very_low=0
    low=0
    medium=0
    high=0
    count = ThreatAnalysisTable.query.filter_by(project_id=pid).count()
    if count > 0:
        very_low = ThreatAnalysisTable.query.filter_by(project_id=pid,attack_level='非常低' ).count()
        low = ThreatAnalysisTable.query.filter_by(project_id=pid,attack_level='低' ).count()
        medium = ThreatAnalysisTable.query.filter_by(project_id=pid,attack_level='中' ).count()
        high = ThreatAnalysisTable.query.filter_by(project_id=pid,attack_level='高' ).count()
    attack_level_data = {
        'labels': ['非常低', '低', '中', '高'],
        'data': [very_low, low, medium, high]
    }

    # 生成风险等级数据
    v1=0
    v2=0
    v3=0
    v4=0
    count = RiskAssessmentEcuTable.query.filter_by(project_id=pid).count()
    if count > 0:
        v1 = RiskAssessmentEcuTable.query.filter_by(project_id=pid, risk_level='1').count()
        v2 = RiskAssessmentEcuTable.query.filter_by(project_id=pid, risk_level='2').count()
        v3 = RiskAssessmentEcuTable.query.filter_by(project_id=pid, risk_level='3').count()
        v4 = RiskAssessmentEcuTable.query.filter_by(project_id=pid, risk_level='4').count()
    risk_level_data = {
        # 'labels': ['1', '2', '3', '4'],
        'labels': ['非常低', '低', '中', '高'],
        'data': [v1, v2, v3, v4]
    }

    return jsonify({
        'attack_level_data': attack_level_data,
        'risk_level_data': risk_level_data
    })
@app.route('/getThreadAssessmentPara')
@login_required
def get_thread_assessment_para():
    type_param = request.args.get('type')
    if not type_param:
        return jsonify({'msg': '缺少type参数'}), 400
    print(f'type_param = {type_param}')
    options = ThreadAssessmentParaTable.query.filter_by(type=type_param).all()
    # for op in options:
    #     print(f'ThreadAssessmentParaTable: {op.id} : {op.name}')
    return jsonify([{'id': option.id, 'name': option.name} for option in options])

@app.route('/updateThreatAnalysis', methods=['POST'])
@login_required
def updateThreatAnalysis():
    data = request.json
    id = data.get('id')
    field = data.get('field')
    value = data.get('value')

    # print(f'updateThreatAnalysis: id={id} field={field}, value={value}')
    threat_analysis = ThreatAnalysisTable.query.get(id)
    if threat_analysis:
        setattr(threat_analysis, field, value)
        auto_calc_attack_level(threat_analysis)

        db.session.commit()
        update_cache_threat_analysis(threat_analysis, field, value)

        return jsonify({'msg': '更新成功'})
    else:
        return jsonify({'msg': '记录不存在,更新失败'})


@app.route('/editThreatAnalysis', methods=['POST'])
@login_required
def edit_threat_analysis():
    data = request.form
    threat_analysis = ThreatAnalysisTable.query.get(data['id'])
    if threat_analysis:
        threat_analysis.ecu_id = data['ecu_id']
        threat_analysis.ecu_feature_id = data['ecu_feature_id']
        threat_analysis.security_attr_id = data['security_attr_id']
        threat_analysis.thread_type_id = data['thread_type_id']
        threat_analysis.attack_path_number = data['attack_path_number']
        threat_analysis.attack_path = data['attack_path']
        threat_analysis.thread_scenario_number = data['thread_scenario_number']
        threat_analysis.thread_scenario = data['thread_scenario']
        threat_analysis.vdt_id = data['vdt_id']
        threat_analysis.asl_id = data['asl_id']
        threat_analysis.knowledge_level_id = data['knowledge_level_id']
        threat_analysis.woo_id = data['woo_id']
        threat_analysis.device_id = data['device_id']
        threat_analysis.attack_value = data['attack_value']
        threat_analysis.attack_level = data['attack_level']
        threat_analysis.project_id = data['project_id']
        db.session.commit()
        return jsonify({'code': 0, 'msg': '威胁分析编辑成功'})
    else:
        return jsonify({'code': 1, 'msg': '威胁分析不存在'})

@app.route('/deleteThreatAnalysis/<int:id>', methods=['DELETE'])
@login_required
def delete_threat_analysis(id):
    threat_analysis = ThreatAnalysisTable.query.get(id)
    if threat_analysis:
        db.session.delete(threat_analysis)
        db.session.commit()
        return jsonify({'code': 0, 'msg': '威胁分析删除成功'})
    else:
        return jsonify({'code': 1, 'msg': '威胁分析不存在'})

@app.route('/getRiskTreatmentStrategy')
@login_required
def get_risk_treatment_strategy():
    options = RiskTreatmentStrategyTable.query.all()
    return jsonify([{'id': option.id, 'name': option.name} for option in options])

@app.route('/getRiskAssessmentPara')
@login_required
def get_risk_assessment_para():
    type_param = request.args.get('type')
    if not type_param:
        return jsonify({'msg': '缺少type参数'}), 400
    options = RiskAssessmentParaTable.query.filter_by(type=type_param).all()
    return jsonify([{'id': option.id, 'name': option.name} for option in options])

@app.route('/getRiskAssessmentShare', methods=['GET'])
@login_required
def get_risk_assessment_share():
    page = request.args.get('page', default=1, type=int)
    limit = request.args.get('limit', default=10, type=int)
    filter_param = request.args.get('filter')

    # print(f'getRiskAssessmentShare:查看经验库 = {filter_param}')
    risk_assessment_query = RiskAssessmentShareTable.query
    paginated_result = risk_assessment_query.paginate(page=page, per_page=limit, error_out=False)

    result = []
    for risk_assessment in paginated_result.items:
        risk_treatment_desc = ''
        # print(f'risk_assessment = {risk_assessment.id}, {risk_assessment.ecu.abbr}, {risk_assessment.threat_analysis_id}, {risk_assessment.threat_analysis.thread_type_id}')
        if risk_assessment.risk_treatment_strategy_id > 0:
            # print(f'get_risk_assessment_ecu: threat_analysis.thread_type_id={risk_assessment.threat_analysis.thread_type_id}')
            risk_treatment_desc = risk_assessment.risk_treatment_strategy.desc
        try:
            result.append({
                'id': risk_assessment.id,
                'item': risk_assessment.item_name,
                'ecu': risk_assessment.ecu_abbr,
                'ecu_feature': risk_assessment.feature_name,
                'security_attr': risk_assessment.security_attr_name,
                #
                'thread_type': risk_assessment.threat_analysis.thread_type_name,
                'thread_scenario_number': risk_assessment.threat_analysis.thread_scenario_number,
                'thread_scenario': risk_assessment.threat_analysis.thread_scenario,
                #
                'damage_scenario_desc': risk_assessment.damage_scenario_desc,
                # 'risk_code': risk_assessment.risk_code,

                'safe_affect_id': risk_assessment.safe_affect_id,
                'economy_affect_id': risk_assessment.economy_affect_id,
                'operator_affect_id': risk_assessment.operator_affect_id,
                'private_affect_id': risk_assessment.private_affect_id,

                'affect_value': risk_assessment.affect_value,
                'affect_level': risk_assessment.affect_level,
                'risk_level': risk_assessment.risk_level,
                'attack_level': risk_assessment.threat_analysis.attack_level,
                'risk_treatment_strategy_id': risk_assessment.risk_treatment_strategy_id,
                'risk_treatment_desc': risk_treatment_desc
            })
        except AttributeError as e:
            print(f"Error getRiskAssessmentEcu[{risk_assessment.id}]: {e}")

    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': paginated_result.total,
        'data': result
    })



@app.route('/getRiskAssessmentEcu', methods=['GET'])
@login_required
def get_risk_assessment_ecu():
    page = request.args.get('page', default=1, type=int)
    limit = request.args.get('limit', default=10, type=int)
    filter_param = request.args.get('filter')
    print(f'args: {request.args}')


    pid = current_user.project_id
    print(f'getRiskAssessmentEcu:filter_param= {filter_param}')
    if filter_param == 'True':
        # 如果filter为True，返回过滤后的数据
        risk_assessment_query = RiskAssessmentEcuTable.query.filter(
            RiskAssessmentEcuTable.project_id==pid,
            (db.func.length(RiskAssessmentEcuTable.damage_scenario_desc) < 6) | (RiskAssessmentEcuTable.affect_value == 0)
        )

    elif filter_param == 'debug':
        risk_assessment_query = RiskAssessmentEcuTable.query.filter_by(project_id=pid, item_id=1)
    else:
        filter_item = request.args.get('filter_item')
        filter_ecu = request.args.get('filter_ecu')
        filter_feature = request.args.get('filter_feature')
        filter_security = request.args.get('filter_security')
        filter_affect_level = request.args.get('filter_affect_level')
        print(f'filter_item: {filter_item}, filter_ecu: {filter_ecu}, filter_feature: {filter_feature}')

        # 初始化查询
        risk_assessment_query = RiskAssessmentEcuTable.query.filter_by(project_id=pid)

        # 根据过滤条件动态添加过滤
        if filter_affect_level:
            risk_assessment_query = risk_assessment_query.filter(RiskAssessmentEcuTable.affect_level.like(f'%{filter_affect_level}%'))
        if filter_item:
            risk_assessment_query = risk_assessment_query.join(ItemTable).filter(ItemTable.name.like(f'%{filter_item}%'))
        if filter_ecu:
            risk_assessment_query = risk_assessment_query.join(EcuTable).filter(EcuTable.abbr.like(f'%{filter_ecu}%'))
        if filter_feature:
            risk_assessment_query = risk_assessment_query.join(EcuFeatureTable).filter(
                EcuFeatureTable.feature.like(f'%{filter_feature}%'))
        if filter_security:
            risk_assessment_query = risk_assessment_query.join(SecurityAttrTable).filter(
                SecurityAttrTable.name.like(f'%{filter_security}%'))


    paginated_result = risk_assessment_query.paginate(page=page, per_page=limit, error_out=False)

    result = []
    for risk_assessment in paginated_result.items:
        risk_treatment_desc=''
        # print(f'risk_assessment = {risk_assessment.id}, {risk_assessment.ecu.abbr}, {risk_assessment.threat_analysis_id}, {risk_assessment.threat_analysis.thread_type_id}')
        if risk_assessment.risk_treatment_strategy_id > 0:
            # print(f'get_risk_assessment_ecu: threat_analysis.thread_type_id={risk_assessment.threat_analysis.thread_type_id}')
            risk_treatment_desc = risk_assessment.risk_treatment_strategy.desc
        try:
            result.append({
                'id': risk_assessment.id,
                'item_number': risk_assessment.item.number if risk_assessment.item else 'null',
                'item': risk_assessment.item.name,
                'ecu': risk_assessment.ecu.abbr,
                'ecu_feature': risk_assessment.ecu_feature.feature,
                'asset_code': risk_assessment.asset_code,
                'security_attr': risk_assessment.security_attr.name,
                #
                'thread_type': risk_assessment.threat_analysis.thread_type.name,
                'thread_scenario_number': risk_assessment.threat_analysis.thread_scenario_number,
                'thread_scenario': risk_assessment.threat_analysis.thread_scenario,
                'attack_path' : risk_assessment.threat_analysis.attack_path,
                'attack_path_number' : risk_assessment.threat_analysis.attack_path_number,
                #
                'damage_scenario_desc': risk_assessment.damage_scenario_desc,
                'risk_code': risk_assessment.risk_code,

                'safe_affect_id': risk_assessment.safe_affect_id,
                'economy_affect_id': risk_assessment.economy_affect_id,
                'operator_affect_id': risk_assessment.operator_affect_id,
                'private_affect_id': risk_assessment.private_affect_id,

                'affect_value': risk_assessment.affect_value,
                'affect_level': risk_assessment.affect_level,
                'risk_level': get_risk_level_desc(risk_assessment.risk_level),
                'attack_level': risk_assessment.threat_analysis.attack_level,
                # 安全目标
                'cybersecurity_goal_name':risk_assessment.cybersecurity_goal_name if risk_assessment.risk_level and risk_assessment.risk_level in '345' else 'N/A',
                # 安全需求
                'dr_name': risk_assessment.cybersecurity_req.name_or if risk_assessment.cybersecurity_req and risk_assessment.risk_level and risk_assessment.risk_level in '345' else 'N/A',
                'dr_code': risk_assessment.cybersecurity_req.code_or if risk_assessment.cybersecurity_req and risk_assessment.risk_level and risk_assessment.risk_level in '345' else 'N/A',

                'r_risk_est_r_level': risk_assessment.r_risk_est_r_level,
                'r_risk_treatment_strategy':risk_assessment.r_risk_treatment_strategy.name,
                'risk_treatment_strategy_id': risk_assessment.risk_treatment_strategy_id,
                'risk_treatment_desc': risk_treatment_desc
            })
        except AttributeError as e:
            print(f"Error getRiskAssessmentEcu[{risk_assessment.id}]: {e}")

    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': paginated_result.total,
        'data': result
    })


@app.route('/batchUpdateRiskAssessment', methods=['POST'])
@login_required
def batch_update_risk_assessment():
    # 获取请求数据
    data = request.json
    ids = data.get('ids')  # 获取需要更新的记录ID列表
    field = data.get('field')  # 获取需要更新的字段
    value = data.get('value')  # 获取更新的值

    if not ids or not field or value is None:
        return jsonify({'msg': '参数缺失，更新失败'}), 400
    print(f'batch_update_risk_assessment: {ids}, {field}, {value}')
    try:
        # 遍历所有ID，逐条更新记录
        risk_record = None
        for id in ids:
            risk_assessment = RiskAssessmentEcuTable.query.get(id)
            if risk_record is None:
                risk_record = risk_assessment
            if risk_assessment:
                # 保存更新前的值（用于日志记录）
                affect_value_t = risk_assessment.affect_value
                affect_level_t = risk_assessment.affect_level

                # 更新字段值
                setattr(risk_assessment, field, value)

                # 自动计算风险等级
                auto_calc_risk_level(risk_assessment)

                # 提交到数据库
                db.session.commit()

                # 更新缓存（假设有缓存逻辑）
                update_cache_risk_assessment(risk_assessment, field, value)

                # 记录日志
                app.logger.debug(
                    f'{current_user.name} batch_update_risk_assessment({risk_assessment.risk_code}): '
                    f'{affect_value_t}->{risk_assessment.affect_value}, '
                    f'{affect_level_t}->{risk_assessment.affect_level}, '
                    f'{field}:{value}'
                )
        if risk_record:
            return jsonify({'msg': '更新成功', 'affect_value': risk_record.affect_value, 'affect_level': risk_record.affect_level}), 200
        else:
            return jsonify({'msg': '更新失败', 'error': 'ids is None'}), 500

    except Exception as e:
        db.session.rollback()  # 回滚事务
        app.logger.error(f'更新失败: {str(e)}')
        return jsonify({'msg': '批量更新失败', 'error': str(e)}), 500


@app.route('/updateRiskAssessmentEcu', methods=['POST'])
@login_required
def update_risk_assessment_ecu():
    data = request.json
    id = data.get('id')
    field = data.get('field')
    value = data.get('value')

    risk_assessment = RiskAssessmentEcuTable.query.get(id)
    # app.logger.debug(f'before: {risk_assessment.affect_value}, {risk_assessment.affect_level}')
    affect_value_t = risk_assessment.affect_value
    affect_level_t = risk_assessment.affect_level
    if risk_assessment:
        setattr(risk_assessment, field, value)
        auto_calc_risk_level(risk_assessment)
        db.session.commit()
        update_cache_risk_assessment(risk_assessment, field, value)
        app.logger.debug(f'{current_user.name} update_risk_assessment({risk_assessment.risk_code}): {affect_value_t}->{risk_assessment.affect_value}, {affect_level_t}->{risk_assessment.affect_level}, {field}:{value}')

        return jsonify({'msg': '<风险评估表>更新成功', 'affect_value': risk_assessment.affect_value, 'affect_level': risk_assessment.affect_level})

    else:
        return jsonify({'msg': '记录不存在,更新失败'})



@app.route('/getFilteredTara', methods=['GET'])
@login_required
def get_filtered_tara():
    # 获取分页参数
    # page = request.args.get('page', default=1, type=int)
    # limit = request.args.get('limit', default=10, type=int)

    # 查询 RiskAssessmentEcuTable 中 risk_level > 2 的 ecu_id
    # risk_assessment_ecu_ids = db.session.query(RiskAssessmentEcuTable.ecu_id).filter(
    #     cast(RiskAssessmentEcuTable.risk_level, Integer) > 2).all()
    # ecu_ids = [id for (id,) in risk_assessment_ecu_ids]
    # print(f'>2 : {len(ecu_ids)}, {len(risk_assessment_ecu_ids)}')

    result = []
    # 根据 ecu_id 过滤 ThreatAnalysisTable
    threat_analysis_query = ThreatAnalysisTable.query.filter_by(project_id=current_user.project_id).all()
    for ta in threat_analysis_query:
        if above2(ta):
            result.append({
                'id': ta.id,
                'ecu': ta.ecu.abbr,
                'thread_type': ta.thread_type.name,
                'thread_scenario_number': ta.thread_scenario_number,
                'thread_scenario': ta.thread_scenario,
                'attack_level': ta.attack_level
            })
    # print(f'count: {len(result)}')
    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': len(result),
        'data': result
    })

@app.route('/getFilteredRiskAssessmentEcu', methods=['GET'])
@login_required
def get_filtered_risk_assessment_ecu():
    all_args = request.args.to_dict()
    print("所有传递过来的参数:", all_args)

    page = request.args.get('page', default=1, type=int)
    limit = request.args.get('limit', default=10, type=int)
    risk_filter = request.args.get('filter_option', default='all', type=str)
    isFiltered = request.args.get('isFiltered', default='false', type=str)
    print(f'isFiltered = {isFiltered}')

    if isFiltered == 'false':
        filtered_query = (RiskAssessmentEcuTable.query
                          .join(RiskTreatmentStrategyTable, RiskAssessmentEcuTable.risk_treatment_strategy_id == RiskTreatmentStrategyTable.id)
                          .filter(RiskTreatmentStrategyTable.name == '风险降低', RiskAssessmentEcuTable.project_id==current_user.project_id))
        print(f'isFiltered = {isFiltered}, 显示所有  {filtered_query.count()}')
    else:
        filtered_query = (RiskAssessmentEcuTable.query.join(RiskTreatmentStrategyTable, RiskAssessmentEcuTable.risk_treatment_strategy_id == RiskTreatmentStrategyTable.id)
                          .filter(RiskTreatmentStrategyTable.name == '风险降低', RiskAssessmentEcuTable.project_id == current_user.project_id,
                                  or_(RiskAssessmentEcuTable.cybersecurity_req_id is None, RiskAssessmentEcuTable.cybersecurity_req_id == 0)
                                  )
                          )
        print(f'isFiltered = {isFiltered}, 过滤显示 {filtered_query.count()}')
    paginated_result = filtered_query.paginate(page=page, per_page=limit, error_out=False)

    result = []
    for item in paginated_result:
        req_or = ''
        if item.cybersecurity_req_id:
            req_or = item.cybersecurity_req.name_or

        r_level = item.r_risk_est_r_level
        r_risk_treatment_strategy = item.r_risk_treatment_strategy.name
        r_risk_est_explanation= item.r_risk_treatment_strategy.desc
        if r_level=='-':
            r_risk_treatment_strategy = '-'
            r_risk_est_explanation= '-'

        result.append({
            'id': item.id,
            'item': item.item.name,
            'risk_code': item.risk_code,
            'risk_level': item.risk_level,
            'cybersecurity_goal_name': item.cybersecurity_goal_name,
            # 'cybersecurity_goal_code': item.cybersecurity_goal_code,
            'cybersecurity_req_or': req_or,
            'r_risk_est_r_level': r_level,
            'r_risk_treatment_strategy': r_risk_treatment_strategy,
            'r_risk_est_explanation': r_risk_est_explanation
        })

    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': paginated_result.total,
        'data': result
    })
#
@app.route('/get_projects', methods=['GET'])
@login_required
def get_projects():
    """Return a list of project dictionaries containing id and name"""
    # print(f'get_projects========')
    try:
        projects = ProjectTable.query.all()
        return [{"id": project.id, "name": project.name} for project in projects]
    except SQLAlchemyError as e:
        print(f"Database error: {e}")
        return []
@app.route('/handle_select_task', methods=['POST'])
@login_required
def handle_select_task():
    data = request.json  # 使用 request.json 来获取 JSON 格式的数据
    task_id = data.get('id')
    # print(f'selected task id: {task_id}')

    current_user.project_id = task_id
    task = db.session.get(ProjectTable, task_id)
    db.session.commit()
    return jsonify({'status': 'success', 'message': f'当前任务设置成功', 'task_name': f'{task.name}'})


@app.route('/setCybersecurityReq', methods=['POST'])
@login_required
def set_cybersecurity_req():
    req_id = request.form.get('req_id')
    risk_assessment_id = session.get('risk_assessment_id')
    print(f'set_cybersecurity_req: {risk_assessment_id}->{req_id}')
    if risk_assessment_id:
        risk_assessment = db.session.get(RiskAssessmentEcuTable, risk_assessment_id)
        if risk_assessment:
            risk_list = risk_assessment.threat_analysis.risk_assessment_ecu_levels
            # print(f'risk_assessment_ecu_levels: {len(risk_list)}')
            for risk_assessment in risk_list:
                if int(risk_assessment.risk_level) > 2:
                    print(f'set 安全需求: {risk_assessment.id}, {risk_assessment.risk_code}')

                    field = 'cybersecurity_req_id'
                    value = req_id
                    setattr(risk_assessment, field, value)

                    update_cache_risk_assessment(risk_assessment, field, value)

                    field = 'r_attack_feasibility_level'
                    value = '非常低'
                    setattr(risk_assessment, field, value)
                    update_cache_risk_assessment(risk_assessment, field, value)

                    auto_calc_r_risk_level(risk_assessment)

            return jsonify({
                'code': 0,
                'msg': '成功关联设计需求',
                'count': 0,
                'data': ''
            })
    return jsonify({
        'code': 1,
        'msg': '设计需求设定失败，请联系管理员.',
        'count': 0,
        'data': ''
    })

@app.route('/getFilteredCybersecurityReq', methods=['GET'])
@login_required
def get_filtered_cybersecurity_req():
    selected_id = request.args.get('id')
    selected_type = request.args.get('type')
    pid=current_user.project_id

    # print(f'selected_id= {selected_id}, type={selected_type}')
    if not selected_id:
        return jsonify({
            'code': 0,
            'msg': '成功',
            'count': 0,
            'data': ''
        })
    # risk_assessment = db.session.get(RiskAssessmentEcuTable, selected_id)
    record = db.session.get(RiskAssessmentEcuTable, selected_id)

    if not record:
        return jsonify({'msg': '未找到对应的风险评估记录'}), 404

    # 获取对应的 ecu_feature_type
    feature_type = record.ecu_feature.type
    # print(f'Type = {feature_type}')
    # 查询所有符合条件的记录，并按 type == feature_type 排序
    reqs = (CybersecurityReqTable.query
            .filter(CybersecurityReqTable.project_id == pid,
                    CybersecurityReqTable.code_dr.ilike("%01"))
            .order_by(db.desc(CybersecurityReqTable.type == feature_type), CybersecurityReqTable.type)
            .all())

    session['risk_assessment_id'] = selected_id
    result = []
    for req in reqs:
        result.append({
            'id': req.id,
            'type': req.type,
            'name_or': req.name_or,
            'desc_or': req.desc_or,
            'name_dr': req.name_dr,
            'desc_dr': req.desc_dr,
            'state': req.state
        })
    print(f'getFilteredCybersecurityReq() return = {result}')
    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': len(result),
        'data': result
    })
@app.route('/checkProjectName', methods=['POST'])
@login_required
def check_project_name():
    name = request.form.get('name')
    exists = ProjectTable.query.filter_by(name=name).first() is not None
    print(exists)
    return jsonify({'exists': exists})

@app.route('/task_management')
@login_required
def task_management():
    app.logger.info(f'user:{current_user.uid} {current_user.name}, Open task_management')

    # 获取用户的任务 ID
    task_id = current_user.project_id
    # 渲染模板并传递任务 ID
    return render_template('ItemDefinition/task_management.html', selected_task_id=task_id)


@app.route('/get_tasks')
@login_required
def get_tasks():
    app.logger.debug(f'user:{current_user.uid} {current_user.name}, get_tasks')
    # 直接使用 current_user 获取当前登录用户
    user = current_user

    # 根据用户角色查询项目
    if user.role == 1:
        projects = ProjectTable.query.all()
    else:
        projects = user.customer.projects

    # 构建项目列表
    project_list = []
    for project in projects:
        risk_h_count = (RiskAssessmentEcuTable.query.filter(
            RiskAssessmentEcuTable.project_id == project.id,
            or_(RiskAssessmentEcuTable.risk_level =='3', RiskAssessmentEcuTable.risk_level=='4')).count())
        risk_with_req =  (RiskAssessmentEcuTable.query.filter(
            RiskAssessmentEcuTable.project_id == project.id,
            or_(RiskAssessmentEcuTable.risk_level == '3', RiskAssessmentEcuTable.risk_level == '4'),
            RiskAssessmentEcuTable.cybersecurity_req_id is not None,
            RiskAssessmentEcuTable.cybersecurity_req_id>0).count())
        project_list.append({
            'id': project.id,
            'name': project.name,
            'desc': project.desc,
            'abbr': project.abbr,
            'tara_info': f'{RiskAssessmentEcuTable.query.filter_by(project_id=project.id).count()}',
            'ecu_info': f'{EcuTable.query.filter_by(Project_id=project.id).count()}',
            'item_info': f'{ItemTable.query.filter_by(project_id=project.id).count()}',
            'risk_info': f'{risk_with_req}/{risk_h_count}',
            'req_info': 'TODO',
            'customer': project.customer.name,
            'state': project.state,
            'pm': project.pm,
            'create_time': project.create_time,
            'update_time': project.update_time
        })

    # 返回 JSON 响应
    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': len(project_list),
        'data': project_list
    })

@app.route('/add_task', methods=['POST'])
@login_required  # 确保用户已登录
def add_task():
    data = request.get_json()
    print(f'add task: {data}')
    current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    # 直接使用 current_user 获取当前登录用户
    user = current_user
    # 创建新项目
    new_project = ProjectTable(
        name=data['task_name'],
        customer_id=user.customer_id,
        desc=data['task_desc'],
        pm=data['task_pm'],
        abbr=data['task_abbr'],
        state=data['task_status'],
        create_time=current_time,
        update_time=current_time
    )
    app.logger.debug(f'user:{current_user.uid} {current_user.name}, add_task: {new_project} ')

    db.session.add(new_project)
    db.session.commit()

    # 返回成功响应
    return jsonify({
        'code': 0,
        'msg': '创建新任务成功'
    })


@app.route('/edit_task', methods=['POST'])
@login_required
def edit_task():
    data = request.form
    project_id = data.get('id')
    app.logger.debug(f'user:{current_user.uid} {current_user.name}, edit_task: {data}, pid={project_id}')

    project = db.session.get(ProjectTable, int(data['id']))
    if project:
        project.name = data['name']
        project.state = data['state']
        project.abbr = data['abbr']

        project.pm = data['pm']
        project.desc = data['project_desc']
        project.update_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        db.session.commit()
        print(f'edit task, project = {project}')
        return jsonify({
            'code': 0,
            'msg': '编辑成功'
        })
    else:
        app.logger.error(f'user:{current_user.uid} {current_user.name}, edit_task: {data}, pid={project_id}, cannot get ProjectTable!')
        return jsonify({
            'code': 1,
            'msg': '编辑失败'
        })
@app.route('/delete_task/<int:id>', methods=['DELETE'])
@login_required
def delete_task(id):
    project = db.session.get(ProjectTable, int(id))
    app.logger.info(f'user:{current_user.uid} {current_user.name}, delete_task: {id}, project={project}')
    if project:
        db.session.delete(project)
        db.session.commit()
        return jsonify({
            'code': 0,
            'msg': '任务删除成功'
        })
    else:
        app.logger.info(f'user:{current_user.uid} {current_user.name}, delete_task: {id}, project not found.')
        return jsonify({
            'code': 1,
            'msg': '任务不存在'
        })


@app.route('/addProjectForm')
@login_required
def add_project_form():
    app.logger.info(f'user:{current_user.uid} {current_user.name}, addProjectForm()')
    return render_template('ItemDefinition/addTaskForm.html')
@app.route('/editProjectForm')
@login_required
def edit_project_form():
    pid = request.args.get('id')
    app.logger.info(f'user:{current_user.uid} {current_user.name}, editProjectForm({pid})')
    project = db.session.get(ProjectTable, int(pid))
    return render_template('ItemDefinition/editTaskForm.html', project=project)


@app.route('/upload', methods=['POST'])
@login_required
def upload():
    file = request.files['file']
    table_name = request.form['tableName']
    pid = current_user.project_id
    app.logger.info(f'user:{current_user.uid} {current_user.name}, /upload:{file.filename} - {table_name}, pid={pid}')
    print(f'user:{current_user.uid} {current_user.name}, /upload:{file.filename} - {table_name}, pid={pid}')
    if file and table_name:
        file_path = save_file_with_timestamp(file)  # 保存文件并获取文件路径
        sheet_name = 'Sheet1'
        if table_name == 'EcuFeatureTable':  # 根据表名导入数据
            create_ecu_feature_table(pid, file_path, sheet_name)
            return jsonify({'msg': f'{table_name}表成功导入.'})

        if table_name == 'FeatureMapSecurityAttrTable':  # 根据表名导入数据
            print(f'==> {table_name}')
            c = create_feature_map_security_attr_table(pid, file_path, sheet_name)
            return jsonify({'msg': f'{table_name}表导入生成 {c} 条记录.'})

        elif table_name == 'ThreadAssessmentParaTable':
            create_thread_assessment_para_table(pid, file_path, sheet_name)
            return jsonify({'msg': f'{table_name}表成功导入.'})

        elif table_name == 'RiskAssessmentParaTable':
            create_risk_assessment_para_table(pid, file_path, sheet_name)
            return jsonify({'msg': f'{table_name}表成功导入.'})

        # added 2025-02-04
        elif table_name == 'ItemImpactLevelTable':
            c = import_item_impact_table(pid, file_path, '表1-Item影响级别')
            return jsonify({'msg': f'{table_name}表导入 {c} 条记录.'})
        elif table_name == 'TaraRuleMappingTable':
            import_tara_rule_mapping_table(pid, file_path, sheet_name)
            return jsonify({'msg': f'{table_name}表导入OK.'})

        elif table_name == 'EcuTable':  # 07
            create_ecu_table_new(pid, file_path, '零部件网络安全相关性')
            # create_map_view()
            # print(f'==> {table_name}, file={file.filename} / {file_path}')
            return jsonify({'msg': f'{table_name}表成功导入.'})

        # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
        elif table_name == 'FuncRelationTable':  # 导入8.数据
            if '相关性' in file.filename:
                new_functions = create_func_relation_table(pid, file_path, '车辆功能清单')
                new_items = update_item_table(pid)
                return jsonify({'msg': f'<> 导入{new_functions}条功能相关性数据, 自动生成{new_items}条Item表项'})
            else:
                return jsonify({'msg': f'上传的文件({file.filename}) 不是功能相关性数据，请重新选择文件'})

        elif table_name == 'CybersecurityReqTable':
            app.logger.info(f'user:{current_user.uid} {current_user.name}, 导入CybersecurityReqTable表:{file.filename}')
            count = import_cybersecurity_req_table(pid, file_path, '网络安全需求基线') # '网络安全需求基线'
            return jsonify({'msg': f'成功导入{count}条安全需求', "status": "success"})

        elif table_name == 'taraTable': # 导入修改后的TARA表
            app.logger.info(f'user:{current_user.uid} {current_user.name}, 导入TARA表:{file.filename}')
            count = import_tara_table(pid, file_path, 'TARA评估表') # '网络安全需求基线'
            return jsonify({'msg': f'成功刷新{count}条TARA表', "status": "success"})

        else:
            warning_string = f'导入失败, 无法识别表 <{table_name}> '
            app.logger.warning(f'{warning_string}, user:{current_user.uid} {current_user.name}, pid={pid}')
            return jsonify({'msg': warning_string})
    else:
        app.logger.warning(f'Warning: 文件或表名缺失, user:{current_user.uid} {current_user.name}, /upload:{file.filename} - {table_name}, pid={pid}')
        return jsonify({'msg': '系统异常，导入失败'})

@app.route('/editFunctionForm', methods=['GET'])
@login_required
def edit_function_form():
    function_id = request.args.get('id')
    function = FuncRelationTable.query.get(function_id)
    items = ItemTable.query.filter_by(project_id=current_user.project_id).all()
    app.logger.info(f'user:{current_user.uid} {current_user.name}, editFunctionForm:{function_id} - {items}')

    return render_template('ItemDefinition/editFunctionForm.html', function=function, items=items)

@app.route('/setSessionProjectId/<int:project_id>', methods=['POST'])
@login_required
def set_session_project_id(project_id):
    try:
        # 将项目ID存储到会话中
        current_user.project_id = project_id


        db.session.commit()
        return jsonify({'msg': '当前项目设定成功', 'code': 200})
    except Exception as e:
        print(f'err: pid: {project_id}, {str(e)}')
        return jsonify({'msg': f'当前项目设定失败: {str(e)}', 'code': 500})


@app.route('/clearTable/<tableName>', methods=['DELETE'])
def clear_table(tableName):
    model_class = globals().get(tableName)
    if model_class is None:
        raise ValueError(f"Class {tableName} not found")

    # 获取类对应的表名
    table_name = model_class.__tablename__
    print(f'clear_table: {tableName}->{table_name}')
    # 获取数据库的元数据
    metadata = MetaData()
    metadata.reflect(bind=db.engine)

    # 获取指定表的Table对象
    table = metadata.tables.get(table_name)

    if table is None:
        raise ValueError(f"clear_table(): Table {table_name} not found.")

    # 删除表中的所有数据
    db.session.execute(table.delete())
    db.session.commit()
    return jsonify({'msg': f'已清空<{tableName}> '})
    # if tableName == 'EcuTable':
    #     EcuTable.query.delete()
    #     db.session.commit()
    #     return jsonify({'msg': f'Ecu列表已清空 - {EcuTable.query.count()}'})
    # else:
    #     return jsonify({'msg': '暂不支持该表的清空'})

# 获取ECU特性列表
@app.route('/getEcuFeatures')
def get_ecu_features():
    ecu_features = EcuFeatureTable.query.all()
    feature_list = []

    for feature in ecu_features:
        feature_list.append({
            'id': feature.id,
            'feature': feature.feature,
            'type': feature.type,
            'remark': feature.remark
        })

    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': len(feature_list),
        'data': feature_list
    })

# 添加ECU特性表单
@app.route('/addEcuFeatureForm')
def add_ecu_feature_form():
    return render_template('BaseData/addEcuFeatureForm.html')

# 编辑ECU特性表单
@app.route('/editEcuFeatureForm')
def edit_ecu_feature_form():
    id = request.args.get('id')
    ecu_feature = db.session.get(EcuFeatureTable, int(id))
    return render_template('BaseData/editEcuFeatureForm.html', ecu_feature=ecu_feature)

# 添加ECU特性
@app.route('/addEcuFeature', methods=['POST'])
def add_ecu_feature():
    data = request.form
    new_feature = EcuFeatureTable(
        feature=f'{int(EcuFeatureTable.query.count()+1):02d}_{data["feature"]}',
        type=data['type'],
        remark=data['remark']
    )
    db.session.add(new_feature)
    db.session.commit()
    return jsonify({
        'code': 0,
        'msg': 'ECU特性添加成功'
    })


@app.route('/addEcu', methods=['POST'])
def add_ecu():
    try:
        # 获取前端发送的表单数据
        data = request.form.to_dict()

        # 打印接收到的数据以供调试
        print("addEcu Received data:", data)

        # 验证数据（简单示例）
        required_fields = ['name', 'abbr', 'OS']
        for field in required_fields:
            if field not in data or not data[field]:
                return jsonify({"code": 1, "msg": f"缺少必填字段: {field}"})

        # 创建 EcuTable 实例并保存到数据库
        ecu = EcuTable(
            name=data.get('name'),
            abbr=data.get('abbr'),
            pcb=data.get('pcb'),
            chipset=data.get('chipset'),
            Bootloader=data.get('Bootloader'),
            OS=data.get('OS'),
            Linux=data.get('Linux'),
            Windows=data.get('Windows'),
            Android=data.get('Android'),
            QNX=data.get('QNX'),
            HarmonyOS=data.get('HarmonyOS'),
            RTOS=data.get('RTOS'),
            app=data.get('app'),
            app_3rd=data.get('app_3rd'),
            data_1=data.get('data_1'),
            data_2=data.get('data_2'),
            data_3=data.get('data_3'),
            interface_usb=data.get('interface_usb'),
            interface_sd=data.get('interface_sd'),
            interface_debug=data.get('interface_debug'),
            can_bus=data.get('can_bus'),
            eth_bus=data.get('eth_bus'),
            wireless_bt=data.get('wireless_bt'),
            wireless_wifi=data.get('wireless_wifi'),
            wireless_rf=data.get('wireless_rf'),
            wireless_mobile=data.get('wireless_mobile'),
            wireless_gnss=data.get('wireless_gnss'),
            wireless_v2x=data.get('wireless_v2x'),
            ota_upgrade=data.get('ota_upgrade'),
            usb_upgrade=data.get('usb_upgrade'),
            obd_debug=data.get('obd_debug'),
            intrusion_detection_prevention_systems=data.get('intrusion_detection_prevention_systems'),
            Project_id=current_user.project_id
        )

        # 将数据保存到数据库
        db.session.add(ecu)
        db.session.commit()
        create_ecu_map_feature_table(ecu,current_user.project_id)
        print(f'db.add_ecu: {ecu}')
        # 返回成功响应
        return jsonify({"code": 0, "msg": "ECU 添加成功"})

    except Exception as e:
        # 捕获异常并返回错误信息
        db.session.rollback()
        return jsonify({"code": 1, "msg": f"服务器错误: {str(e)}"})




# 添加功能
@app.route('/add_function', methods=['POST'])
@login_required
def add_function():
    data = request.form
    # print(f'add_function: {data}')
    bool_ee = 'Y' if data.get('isEEComponent', 'N') == 'on' else 'N'
    bool_safe = 'Y' if data.get('isSafetyRelevant', 'N') == 'on' else 'N'
    bool_userdata = 'Y' if data.get('isDataSensitive', 'N') == 'on' else 'N'
    bool_network = 'Y' if data.get('isNetworkBased', 'N') == 'on' else 'N'
    check = check_data_flow(data['dataFlow'], EcuTable, current_user.project_id)
    result = 'N'
    if bool_ee == 'Y':
        if bool_safe == 'Y' or bool_userdata == 'Y' or bool_network == 'Y':
            result = 'Y'

    func = FuncRelationTable(
        name = data['function'],
        desc = data['description'],
        data_flow = data['dataFlow'],
        func_domain_id = data['domain'],
        contact_person = data['contact_person'],
        check = check,
        bool_ee = bool_ee,
        bool_safe = bool_safe,
        bool_userdata = bool_userdata,
        bool_network = bool_network,
        project_id = current_user.project_id,
        result = result
    )
    db.session.add(func)
    db.session.commit()
    return jsonify({
        'code': 0,
        'msg': '新功能加成功'
    })

@app.route('/edit_function', methods=['POST'])
@login_required
def edit_function():
    data = request.form
    print(f'edit_function: {data}')
    bool_ee = 'Y' if data.get('isEEComponent', 'N') == 'on' else 'N'
    bool_safe = 'Y' if data.get('isSafetyRelevant', 'N') == 'on' else 'N'
    bool_userdata = 'Y' if data.get('isDataSensitive', 'N') == 'on' else 'N'
    bool_network = 'Y' if data.get('isNetworkBased', 'N') == 'on' else 'N'
    result = 'N'
    if bool_ee == 'Y':
        if bool_safe == 'Y' or bool_userdata == 'Y' or bool_network == 'Y':
            result = 'Y'

    function = FuncRelationTable.query.get(data['id'])
    if function:
        function.name = data['function']
        function.desc = data['description']
        function.func_domain_id = data['domain']
        function.data_flow = data['dataFlow']
        function.contact_person = data['contact_person']
        function.bool_ee = bool_ee
        function.bool_safe = bool_safe
        function.bool_userdata = bool_userdata
        function.bool_network = bool_network
        function.result = result
        check = check_data_flow(data['dataFlow'], EcuTable, current_user.project_id)
        function.check = check
        if result == 'Y':
            function.func_domain.ecu_list = merge_string(function.func_domain.ecu_list, check)
        db.session.commit()

        return jsonify({'status': 'success', 'msg': '编辑成功'})
    return jsonify({'status': 'error', 'msg': '编辑失败'})

@app.route('/delete_function', methods=['POST'])
@login_required
def delete_function():
    function_id = request.form.get('id')
    function = FuncRelationTable.query.get(function_id)
    if function:
        db.session.delete(function)
        db.session.commit()
        return jsonify({'status': 'success', 'msg': '删除成功'})
    return jsonify({'status': 'error', 'msg': '删除失败'})




# 编辑ECU特性
@app.route('/editEcuFeature', methods=['POST'])
@login_required
def edit_ecu_feature():
    data = request.form
    ecu_feature = db.session.get(EcuFeatureTable, data['id'])
    if ecu_feature:
        ecu_feature.feature = data['feature']
        ecu_feature.type = data['type']
        ecu_feature.remark = data['remark']
        db.session.commit()
        return jsonify({
            'code': 0,
            'msg': 'ECU特性编辑成功'
        })
    else:
        return jsonify({
            'code': 1,
            'msg': 'ECU特性不存在'
        })

# 删除ECU特性
@app.route('/deleteEcuFeature/<int:id>', methods=['DELETE'])
@login_required
def delete_ecu_feature(id):
    ecu_feature = db.session.get(EcuFeatureTable, id)
    if ecu_feature:
        db.session.delete(ecu_feature)
        db.session.commit()
        return jsonify({
            'code': 0,
            'msg': 'ECU特性删除成功'
        })
    else:
        return jsonify({
            'code': 1,
            'msg': 'ECU特性不存在'
        })

@app.route('/sync_req', methods=['POST'])
@login_required
def sync_req():
    # 这里实现同步拷贝需求表的逻辑
    try:
        time.sleep(1)
        pid=current_user.project_id
        count = duplicate_req_to_project(pid)
        # task = ProjectTable.query.get(pid)
        return jsonify(code=0, msg=f"同步成功: 同步到当前项目{count}条安全需求")

    except Exception as e:
        return jsonify(code=1, msg=str(e))
@app.route('/getCybersecurityReq', methods=['GET'])
@login_required
def get_cybersecurity_req():
    page = request.args.get('page', default=1, type=int)
    limit = request.args.get('limit', default=10, type=int)
    # 只显示pid=0 的基础需求库
    # pid=current_user.project_id

    # 定义窗口函数
    # row_number_window = over(
    #     func.row_number(),
    #     partition_by=CybersecurityReqTable.name_or,  # 按分类字段分组
    #     order_by=CybersecurityReqTable.id  # 按 id 排序
    # )
    #
    # # 查询并过滤
    # query = db.session.query(CybersecurityReqTable).add_column(row_number_window.label('row_num'))
    # # 使用 subquery() 创建子查询
    # subquery = query.subquery()
    #
    # filtered_records = db.session.query(subquery).filter(func.row_number() == 1, CybersecurityReqTable.project_id==pid).all()
    cybersecurity_req_query = CybersecurityReqTable.query.filter_by(project_id=0).order_by(CybersecurityReqTable.name_or)
    # cybersecurity_req_query = CybersecurityReqTable.query.filter_by(project_id=pid).order_by(CybersecurityReqTable.name_or)
    paginated_result = cybersecurity_req_query.paginate(page=page, per_page=limit, error_out=False)

    result = []
    for req in paginated_result.items:
        result.append({
            'id': req.id,
            'type': req.type,
            'feature': req.feature if req.feature else '/',
            'name_or': req.name_or,
            'code_or': req.code_or,
            'desc_or': req.desc_or,
            'name_dr': req.name_dr,
            'code_dr': req.code_dr,
            'desc_dr': req.desc_dr,
            'state': req.state,
            'ca_level': req.ca_level,
            'cal1': req.cal1,
            'cal2': req.cal2,
            'cal3': req.cal3,
            'cal4': req.cal4,
            'R155': req.r155,
            'R156': req.r156,
            'GB 44495': req.gb44495,
            'GB 44496': req.gb44496,
            'gb_new_req': req.gb_new_req,
            'other': req.other,
            'remark': req.remark,
            'req_basis': req.req_basis
        })

    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': paginated_result.total,
        'data': result
    })
@app.route('/get_func_relations', methods=['GET'])
@login_required
def get_func_relations():
    page = request.args.get('page', default=1, type=int)
    limit = request.args.get('limit', default=10, type=int)

    # 获取过滤条件
    item_id = request.args.get('func_domain')
    if item_id:
        print(f'item filter: {item_id}')
        func_relation_query = FuncRelationTable.query.filter_by(project_id=current_user.project_id,func_domain_id=item_id)
    else:
        func_relation_query = FuncRelationTable.query.filter_by(project_id=current_user.project_id)

    paginated_result = func_relation_query.paginate(page=page, per_page=limit, error_out=False)

    result = []
    for req in paginated_result.items:
        result.append({
            'id': req.id,
            'name': req.name,
            'desc': req.desc,
            'item': req.item,
            'system': req.system,
            'data_flow': req.data_flow,
            'contact_person': req.contact_person,
            'bool_ee': req.bool_ee,
            'bool_safe': req.bool_safe,
            'bool_userdata': req.bool_userdata,
            'bool_network': req.bool_network,
            'result': req.result
        })

    return jsonify({
        'code': 0,
        'msg': '成功',
        'count': paginated_result.total,
        'data': result
    })

@app.route('/updateFuncRelation', methods=['POST'])
@login_required
def update_func_relation():
    data = request.json
    id = data.get('id')
    field = data.get('field')
    value = data.get('value')
    # print(f'update_func_relation: {id}, {field}, {value}')
    func_relation = FuncRelationTable.query.get(id)
    if func_relation:
        setattr(func_relation, field, value)
        db.session.commit()
        return jsonify({'msg': '更新成功'})
    else:
        return jsonify({'msg': '记录不存在,更新失败'})

@app.route('/addCybersecurityReqForm')
@login_required
def add_cybersecurity_req_form():
    return render_template('RiskAssessment/addCybersecurityReqForm.html')

@app.route('/editCybersecurityReqForm')
@login_required
def edit_cybersecurity_req_form():
    id = request.args.get('id')
    req = db.session.get(CybersecurityReqTable, int(id))
    return render_template('RiskAssessment/editCybersecurityReqForm.html', req=req)

@app.route('/addCybersecurityReq', methods=['POST'])
@login_required
def add_cybersecurity_req():
    data = request.form
    pid=current_user.project_id
    new_req = CybersecurityReqTable(
        type=data['type'],
        feature=data['feature'],
        name_or=data['name_or'],
        # code_or=data['code_or'],
        desc_or=data['desc_or'],
        name_dr=data['name_dr'],
        # code_dr=data['code_dr'],
        desc_dr=data['desc_dr'],
        # state=data['state'],
        ca_level=data['ca_level'],
        req_basis=data['req_basis'],
        project_id=pid
    )
    db.session.add(new_req)
    db.session.commit()
    return jsonify({
        'code': 0,
        'msg': '网络安全需求添加成功'
    })

@app.route('/editCybersecurityReq', methods=['POST'])
@login_required
def edit_cybersecurity_req():
    data = request.form
    req =  db.session.get(CybersecurityReqTable, int(data['id']))
    if req:
        req.type = data['type']
        req.feature = data['feature']
        req.name_or = data['name_or']
        req.code_or = data['code_or']
        req.desc_or = data['desc_or']
        req.name_dr = data['name_dr']
        req.code_dr = data['code_dr']
        req.desc_dr = data['desc_dr']
        req.state = data['state']
        req.ca_level = data['ca_level']
        req.req_basis = data['req_basis']
        db.session.commit()
        return jsonify({
            'code': 0,
            'msg': '网络安全需求编辑成功'
        })
    else:
        return jsonify({
            'code': 1,
            'msg': '网络安全需求不存在'
        })

@app.route('/deleteCybersecurityReq/<int:id>', methods=['DELETE'])
@login_required
def delete_cybersecurity_req(id):
    req = CybersecurityReqTable.query.get(id)
    if req:
        db.session.delete(req)
        db.session.commit()
        return jsonify({
            'code': 0,
            'msg': '网络安全需求删除成功'
        })
    else:
        return jsonify({
            'code': 1,
            'msg': '网络安全需求不存在'
        })

# 刷新整个 Item表
@app.route('/update_item', methods=['POST'])
@login_required
def update_item():
    c = update_item_table(current_user.project_id)
    # print(app.config)  # 打印所有配置，确保 EXECUTOR_PUSH_APP_CONTEXT 存在
    executor.submit(auto_process_item_desc, current_user.project_id)
    print(f'update_item_table: {c}')
    return jsonify({'msg': f'Item表更新成功({c})'})

@app.route('/startTARA', methods=['POST'])
@login_required
def start_tara():
    pid = current_user.project_id
    user_name = current_user.name
    app.logger.debug(f'start TARA分析 : pid={pid}, user_name={user_name}')

    clear_tara_table(pid)
    count = CybersecurityReqTable.query.filter_by(project_id=pid).count()
    print(f'start_tara: 需求表数量 {count}')
    if count==0:
        req_count = duplicate_req_to_project(pid)
        print(f'start_tara: 需求表为空, 同步{req_count}条基础需求到当前车型项目 pid={pid}, user_name={user_name}')

    create_threat_analysis_table(pid)
    count = create_risk_assessment_ecu_table(pid)
    print(f'create_risk_assessment_ecu_table: {count}')
    return jsonify({'msg': f'更新成功'})

@app.route('/export_selected_report', methods=['POST'])
@login_required
def export_selected_report():
    # TODO
    try:
        # 获取 Form 提交的数据
        form_data = request.form

        # 处理选中的报告
        tara_excel = form_data.get('tara_excel')
        tara_word = form_data.get('tara_word')
        reqs_vendor = form_data.get('reqs_vendor')
        reqs_all = form_data.get('reqs_all')
        reqs_word = form_data.get('reqs_word')
        task = ProjectTable.query.get(current_user.project_id)
        app.logger.info(
            f'{current_user.uid}:{current_user.name}, {current_user.project_id}, {task.abbr},'
            f'export_selected_report: form_data={form_data}')
        export_path_with_uid = os.path.join(EXPORT_DIR, str(current_user.uid))
        if not os.path.exists(export_path_with_uid):
            os.makedirs(export_path_with_uid)

        if tara_excel:
            # 生成目标文件名（包含项目简称和时间戳）
            timestamp = datetime.now().strftime("%Y%m%d_%H%M")
            target_filename = f'01 {task.abbr}_TARA数据总表_{timestamp}.xlsx'
            target_path = os.path.join(export_path_with_uid, target_filename)

            # 复制模板文件
            template_path = os.path.join(TEMPLATE_DIR, TEMPLATE_TARA)
            shutil.copy2(template_path, target_path)
            # TODO: 这里可以添加将数据写入Excel文件的逻辑
            ret = export_tara_table_to_excel(target_path, '', current_user.project_id)
        # executor.submit(auto_process_item_desc, current_user.project_id)
        if tara_word:
            print(f'tara_word: {tara_word}')

            # 生成目标文件名（包含项目简称和时间戳）
            target_filename = f'02 {task.abbr}_TARA分析报告_{datetime.now().strftime("%Y%m%d_%H%M")}.docx'
            target_path = os.path.join(export_path_with_uid, target_filename)
            # 复制模板文件
            shutil.copy2(os.path.join(TEMPLATE_DIR, TEMPLATE_TARA_WORD), target_path)
            # 获取TARA数据
            ret = export_tara_report_to_word(target_path, current_user.project_id)

        if reqs_vendor:
            ecus = EcuTable.query.filter_by(Project_id=current_user.project_id).all()
            export_path = os.path.join(export_path_with_uid,f'{task.abbr}_零部件安全需求分配表(ALL)')
            print(f'reqs_vendor: {reqs_vendor}, export_path={export_path}')
            if not os.path.exists(export_path):
                os.makedirs(export_path)

            timestamp = datetime.now().strftime("%Y%m%d_%H%M")

            for ecu in ecus:
                # 生成目标文件名（包含项目简称和ECU名称）
                target_filename = f'03 零部件安全需求分配表_{ecu.abbr_ext}.xlsx'
                target_path = os.path.join(export_path, target_filename)

                # 调用导出函数
                export_ecu_reqs(r'export_templates/07 XX车型 零部件 安全需求表 v2.0.xlsx',
                                target_path, ecu, current_user.project_id)

            # 将export_path 目录及目录下的所有文件，打成一个ZIP压缩包,文件名是 f'03 {task.abbr}_零部件安全需求分配表.zip' 存放在 EXPORT_DIR
            zip_filename = f'03 {task.abbr}_零部件安全需求分配表_{timestamp}.zip'
            zip_path = os.path.join(export_path_with_uid, zip_filename)
            with zipfile.ZipFile(zip_path, 'w') as zipf:
                for root, dirs, files in os.walk(export_path):
                    for file in files:
                        file_path = os.path.join(root, file)
                        arcname = os.path.relpath(file_path, export_path)
                        zipf.write(file_path, arcname)

            # 删除临时目录
            shutil.rmtree(export_path)

        if reqs_all:
            print(f'reqs_all: {reqs_all}')
            # 生成目标文件名（包含项目简称和时间戳）
            timestamp = datetime.now().strftime("%Y%m%d_%H%M")
            target_filename = f'04 {task.abbr}_整车级安全需求分配表_{timestamp}.xlsx'
            target_path = os.path.join(export_path_with_uid, target_filename)

            # 调用导出函数
            export_all_reqs(target_path, current_user.project_id)  #

        if reqs_word:
            print(f'reqs_word: {reqs_word}')

            # 生成目标文件名（包含项目简称和时间戳）
            timestamp = datetime.now().strftime("%Y%m%d_%H%M")
            target_filename = f'05 {task.abbr}_整车安全需求规范_{timestamp}.docx'
            target_path = os.path.join(export_path_with_uid, target_filename)
            # 调用导出函数
            export_all_reqs_to_word(target_path, current_user.project_id)


        # 这里可以根据 selected_reports 进行实际的导出操作
        return jsonify({'success': True, 'message': '报告已成功导出', 'selected_reports': ''})
    except Exception as e:
        app.logger.error(f'Error: {e}, {current_user.uid}, {current_user.name}, {current_user.project_id}')
        return jsonify({'success': False, 'message': str(e)})


@app.route('/export_safety_req_by_ecu', methods=['POST'])
@login_required
def export_ecu_safety_req():
    try:
        export_path_with_uid = os.path.join(EXPORT_DIR, str(current_user.uid))
        if not os.path.exists(export_path_with_uid):
            os.makedirs(export_path_with_uid)

        # 生成文件名
        filename = f'安全需求分配表_{datetime.now().strftime("%H.%M")}'
        filepath = os.path.join(export_path_with_uid, filename)
        print(f'export_ecu_safety_req() start... {filepath}')

        # export_safety_req_by_ecu(filepath, current_user.project_id)
        export_all_reqs(filepath, current_user.project_id)

        print(f'export_ecu_safety_req() end...')
        return jsonify({'success': True, 'message': '数据导出成功'})

    except Exception as e:
        print(f'export_ecu_safety_req(): {str(e)}')
        return jsonify({'success': False, 'message': str(e)})
def export_cal_req(pid):
    # try:
    export_path_with_uid = os.path.join(EXPORT_DIR, str(current_user.uid))
    if not os.path.exists(export_path_with_uid):
        os.makedirs(export_path_with_uid)

    # 生成文件名
    filename = f'安全需求_{datetime.now().strftime("%H.%M")}'
    filepath = os.path.join(export_path_with_uid, filename)
    print(f'export_ecu_safety_req() start... {filepath}')

    # export_safety_req_by_ecu(filepath, current_user.project_id)
    export_all_reqs(filepath, '', pid)

    print(f'export_ecu_safety_req() end...')
    return jsonify({'success': True, 'message': '数据导出成功'})

    # except Exception as e:
    #     print(f'export_ecu_safety_req(): {str(e)}')
    #     return jsonify({'success': False, 'message': str(e)})

@app.route('/clearAllFiles', methods=['POST'])
@login_required
def clear_all_files():
    try:
        export_path_with_uid = os.path.join(EXPORT_DIR, str(current_user.uid))
        if not os.path.exists(export_path_with_uid):
            os.makedirs(export_path_with_uid)
        # 遍历目录并删除所有文件
        for filename in os.listdir(export_path_with_uid):
            file_path = os.path.join(export_path_with_uid, filename)
            app.logger.info(f'{current_user.uid},{current_user.name},{file_path}')
            if os.path.isfile(file_path):
                os.remove(file_path)
        return jsonify({'success': True, 'message': '所有文件已成功删除'})
    except Exception as e:
        app.logger.error(f'error! clear_all_files(): {str(e)}')
        return jsonify({'success': False, 'message': str(e)})
@app.route('/export_ecu_reqs', methods=['POST'])
@login_required
def export_ecu_reqs_route():
    try:
        ecu_id = request.form.get('ecu_id')
        ecu = EcuTable.query.get(ecu_id)
        print(f'export_ecu_reqs() start... {ecu_id}, {ecu}')
        if not ecu:
            return jsonify({'success': False, 'message': 'ECU not found'}), 404

        export_path_with_uid = os.path.join(EXPORT_DIR, str(current_user.uid))
        if not os.path.exists(export_path_with_uid):
            os.makedirs(export_path_with_uid)

        # 获取当前项目信息
        task = ProjectTable.query.get(current_user.project_id)

        # 生成目标文件名（包含项目简称和ECU名称）
        timestamp = datetime.now().strftime("%Y%m%d_%H%M")
        target_filename = f'{task.abbr}_{ecu.name}_安全需求表_{timestamp}.xlsx'
        target_path = os.path.join(export_path_with_uid, target_filename)

        # 调用导出函数
        export_ecu_reqs(r'export_templates/07 XX车型 零部件 安全需求表 v2.0.xlsx',
                        target_path, ecu, current_user.project_id)

        # 返回文件
        return send_file(
            target_path,
            as_attachment=True,
            download_name=quote(target_filename.encode('utf-8')),
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )

    except Exception as e:
        app.logger.error(f'导出ECU安全需求失败: {str(e)}')
        return jsonify({'success': False, 'message': f'导出失败: {str(e)}'}), 500
    finally:
        # 清理临时文件
        if 'target_path' in locals() and os.path.exists(target_path):
            try:
                time.sleep(5)  # 等待 5 秒
                os.remove(target_path)
            except Exception as e:
                app.logger.error(f'清理临时文件失败: {str(e)}')


@app.route('/export_all_reqs', methods=['GET'])
@login_required
def export_all_reqs_route():
    try:
        # 获取当前项目信息
        task = ProjectTable.query.get(current_user.project_id)
        export_path_with_uid = os.path.join(EXPORT_DIR, str(current_user.uid))
        if not os.path.exists(export_path_with_uid):
            os.makedirs(export_path_with_uid)
        # 生成目标文件名（包含项目简称和时间戳）
        timestamp = datetime.now().strftime("%Y%m%d_%H%M")
        target_filename = f'04 {task.abbr}车型安全需求分配表_{timestamp}.xlsx'
        target_path = os.path.join(export_path_with_uid, target_filename)

        # 调用导出函数
        export_all_reqs(target_path, current_user.project_id)  #

        return send_file(
            target_path,
            as_attachment=True,
            download_name=quote(target_filename.encode('utf-8')),
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
    except Exception as e:
        app.logger.error(f'导出全部安全需求失败: {str(e)}')
        return jsonify({'success': False, 'message': f'导出失败: {str(e)}'}), 500
    finally:
        # 清理临时文件
        if 'target_path' in locals() and os.path.exists(target_path):
            try:
                time.sleep(5)  # 等待 5 秒
                os.remove(target_path)
            except Exception as e:
                app.logger.error(f'清理临时文件失败: {str(e)}')
@app.route('/exportData', methods=['POST'])
def export_data():
    try:
        # 获取用户数据
        tara_data = get_tara_data_from_db(current_user.project_id)
        task = ProjectTable.query.get(current_user.project_id)
        export_path_with_uid = os.path.join(EXPORT_DIR, str(current_user.uid))
        if not os.path.exists(export_path_with_uid):
            os.makedirs(export_path_with_uid)
        # 生成文件名
        filename = f'{task.abbr} TARA分析表 {datetime.now().strftime("%H.%M")}.xlsx'
        filepath = os.path.join(export_path_with_uid, filename)

        # 导出数据到 Excel 文件
        tara_data.to_excel(filepath, index=False)

        return jsonify({'success': True, 'message': '数据导出成功'})
    except Exception as e:
        print(f'ERROR::::: {str(e)}')
        return jsonify({'success': False, 'message': str(e)})

@app.route('/listExportedFiles', methods=['GET'])
def list_exported_files():
    try:
        export_path_with_uid = os.path.join(EXPORT_DIR, str(current_user.uid))
        if not os.path.exists(export_path_with_uid):
            os.makedirs(export_path_with_uid)

        # 列出导出目录下的所有 .xlsx 文件
        # files = [f for f in os.listdir(EXPORT_DIR) if f.endswith('.xlsx')]
        # files = [f for f in os.listdir(export_path_with_uid)]
        files = [f for f in os.listdir(export_path_with_uid) if os.path.isfile(os.path.join(export_path_with_uid, f))]
        return jsonify({'files': files})
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)})

@app.route('/download/<filename>', methods=['GET'])
def download_file(filename):
    try:
        export_path_with_uid = os.path.join(EXPORT_DIR, str(current_user.uid))
        if not os.path.exists(export_path_with_uid):
            os.makedirs(export_path_with_uid)
        # 提供文件下载
        return send_from_directory(export_path_with_uid, filename, as_attachment=True)
    except Exception as e:
        return jsonify({'success': False, 'message': str(e)})


@app.route('/getEcu/<int:ecu_id>')
def get_ecu(ecu_id):
    # 从数据库获取ECU数据
    ecu = EcuTable.query.get(ecu_id)
    if ecu:
        return jsonify(ecu.to_dict())
    return jsonify({'error': 'ECU not found'}), 404


@app.route('/editEcu', methods=['POST'])
def edit_ecu():
    # 打印所有传递过来的参数
    print("editEcu:  request.form:", request.form)

    try:
        ecu_id = request.form.get('id')
        ecu = EcuTable.query.get(ecu_id)

        if not ecu:
            return jsonify({'code': 1, 'msg': '错误: 表项不存在'})

        # 更新ECU数据
        ecu.name = request.form.get('name')
        ecu.abbr = request.form.get('abbr_ext')
        ecu.OS = request.form.get('OS')

        print(f'ecu.eth_bus={ecu.eth_bus}, form.get("eth_bus")={request.form.get("eth_bus")}')

        # 更新复选框字段
        checkbox_fields = ['pcb', 'chipset', 'Bootloader', 'app', 'app_3rd',
                           'data_1', 'data_2', 'data_3', 'data_key', 'data_car_info', 'data_log',
                           'data_export', 'data_cross_border', 'interface_usb', 'interface_sd',
                           'interface_debug', 'can_bus', 'eth_bus', 'wireless_bt', 'wireless_wifi',
                           'wireless_rf', 'wireless_mobile', 'wireless_gnss', 'wireless_v2x',
                           'ota_upgrade', 'usb_upgrade', 'obd_upgrade', 'obd_debug','remote_debug',
                           'intrusion_detection_prevention_systems', 'upgrade_node',
                           'tbox', 'gate_way', 'affect_level', 'attack_type', 'cal_level' ]

        for field in checkbox_fields:
            setattr(ecu, field, request.form.get(field, ''))

        db.session.commit()
        del_ecu_map_feature_table(ecu, current_user.project_id)
        create_ecu_map_feature_table(ecu, current_user.project_id)

        return jsonify({'code': 0, 'msg': '更新成功'})
    except Exception as e:
        db.session.rollback()
        return jsonify({'code': 1, 'msg': f'更新失败: {str(e)}'})
######################################################################################################
@app.cli.command('cmd_del_threat_table')
@click.argument('pid', type=int)
@with_appcontext
def cmd_del_threat_table(pid):
    ThreatAnalysisTable.query.filter_by(project_id=pid).delete(synchronize_session=False)
    db.session.commit()
    print(f'delete all pid={pid} ThreatAnalysisTable.')

@app.cli.command('cmd_del_tara_table')
@click.argument('pid', type=int)
@with_appcontext
def cmd_del_tara_table(pid):
    deleted_count=RiskAssessmentEcuTable.query.filter_by(project_id=pid).delete(synchronize_session=False)
    deleted_count2=ThreatAnalysisTable.query.filter_by(project_id=pid).delete(synchronize_session=False)
    db.session.commit()
    print(f'delete all pid={pid} {deleted_count2} , {deleted_count}')
@app.cli.command('cmd_del_risk_table_none')
@with_appcontext
def cmd_del_risk_table_none():
    deleted_count=RiskAssessmentShareTable.query.filter_by(damage_scenario_desc=None).delete(synchronize_session=False)
    deleted_count+=RiskAssessmentShareTable.query.filter_by(threat_analysis_id=None).delete(synchronize_session=False)
    db.session.commit()
    print(f'delete empty riskAssessmentEcuTable. {deleted_count}')

@app.cli.command('cmd_del_threat_table_none')
# @click.argument('pid', type=int)
@with_appcontext
def cmd_del_threat_table_none():
    # 使用 SQLAlchemy 的条件查询来过滤 attack_path 为空或长度小于 5 的记录
    # deleted_count = ThreatAnalysisTable.query.filter(
    #     (ThreatAnalysisTable.project_id == pid) &
    #     ((ThreatAnalysisTable.attack_path.is_(None)) | (db.func.length(ThreatAnalysisTable.attack_path) < 5))
    # ).delete(synchronize_session=False)
    deleted_count = ThreatAnalysisShareTable.query.filter_by(thread_type_name=None).delete(synchronize_session=False)
    db.session.commit()
    print(f'delete {deleted_count} records.')


@app.cli.command('cmd_create_risk_table')
@click.argument('pid', type=int)
@with_appcontext
def cmd_create_risk_table(pid):
    # test_internet()
    count = create_risk_assessment_ecu_table(pid)
    print(f'create RiskAssessmentEcuTable. <{count}>')

@app.cli.command('cmd_auto_process')
@with_appcontext
def cmd_auto_process():
    if test_internet():
        print(f'网络连接正常, 开始自动处理...')
        p1 = auto_process_threat_analysis()
        p2 = auto_process_risk_assessment()
        if p1>0 or p2>0:
            sync_all_tara_table()
    else:
        print(f'网络未连接, 异常退出...')

@app.cli.command('cmd_show_count')
@click.argument('pid', type=int)
@with_appcontext
def cmd_show_count(pid):
    count = ThreatAnalysisTable.query.filter_by(project_id=pid).count()
    print(f'ThreatAnalysisTable: {count}')

    count = RiskAssessmentEcuTable.query.filter_by(project_id=pid).count()
    print(f'RiskAssessmentEcuTable: {count}')

    count = ThreatAnalysisShareTable.query.count()
    print(f'ThreatAnalysisShareTable: {count}')

    count = RiskAssessmentShareTable.query.count()
    print(f'RiskAssessmentShareTable: {count}')
    run_plant_uml()


@app.cli.command('cmd_sync_item_table_plantuml')
@click.argument('pid_s', type=int)
@click.argument('pid_d', type=int)
@with_appcontext
def cmd_sync_item_table_plantuml(pid_s, pid_d):
    sync_item_table_plantuml(pid_s, pid_d)

@app.cli.command('cmd_change_pwd')
@click.argument('user_name', type=str)
@click.argument('pwd', type=str)
@with_appcontext
def cmd_change_pwd(user_name, pwd):
    # 这里可以添加修改密码的逻辑
    print(f'User Name: {user_name}')

    user = UserTable.query.filter_by(name=user_name).first()
    if user is None:
        print(f'User {user_name} does not exist.')
    else:
        user.pwd = generate_password_hash(pwd)
        db.session.commit()
        print(f'User {user_name} 修改密码成功. new: {pwd}')
    # 例如：update_user_password(user_name, pwd)

@app.cli.command('test_format_data_flow')
@click.argument('flow', type=str)
@with_appcontext
def test_format_data_flow(flow):
    print(f'{flow} | {format_data_flow(flow)}')

@app.cli.command('cmd_initdb')
# @click.argument('pid', type=int)
@with_appcontext
def cmd_initdb():
    print(initdb())

@app.cli.command('cmd_export_reqs')
@click.argument('pid', type=int)
@with_appcontext
def cmd_export_reqs(pid):
    export_cal_req(pid)

@app.cli.command('cmd_create_item_uml')
@click.argument('pid', type=int)
@with_appcontext
def cmd_create_item_uml(pid):
    create_item_table_uml(pid)

@app.cli.command('cmd_get_impact_level')
@click.argument('item_level', type=str)
@click.argument('ecu_level', type=str)
@with_appcontext
def cmd_get_impact_level(item_level, ecu_level):
    result = get_impact_level(item_level, ecu_level)
    print(f'result: {result}')

@app.cli.command('cmd_auto_uml')
@click.argument('pid', type=int)
@with_appcontext
def cmd_auto_uml(pid):
    items = ItemTable.query.filter_by(project_id=pid).all()
    for ind, item in enumerate(items, start=1):
        print(f'cmd_auto_uml: {ind}/{len(items)}')
        auto_plant_uml(item)

@app.cli.command('cmd_item_desc')
@click.argument('pid', type=int)
@with_appcontext
def cmd_item_desc(pid):
    auto_process_item_desc(pid)
#
# @app.cli.command('cmd_copy_share')
# @with_appcontext
# def cmd_copy_share():
#     copy_tara_table_to_share()

# 添加在其他路由前
TEMPLATE_DIR = 'export_templates'
TEMPLATE_TARA = '05 网络安全威胁分析与风险评估(TARA)表 v2.0.xlsx'
TEMPLATE_TARA_WORD = '06 XXX车型 XXX item 网络安全威胁分析和风险评估(TARA)报告 v2.1.docx'


@app.route('/export_tara_word', methods=['GET'])
@login_required
def export_tara_word():
    try:
        # 获取当前项目信息
        task = ProjectTable.query.get(current_user.project_id)
        export_path_with_uid = os.path.join(EXPORT_DIR, str(current_user.uid))
        if not os.path.exists(export_path_with_uid):
            os.makedirs(export_path_with_uid)
        # 生成目标文件名（包含项目简称和时间戳）
        timestamp = datetime.now().strftime("%Y%m%d_%H%M")
        target_filename = f'02 {task.abbr}_TARA报告_{timestamp}.docx'
        target_path = os.path.join(export_path_with_uid, target_filename)
        # 复制模板文件
        template_path = os.path.join(TEMPLATE_DIR, TEMPLATE_TARA_WORD)

        shutil.copy2(template_path, target_path)
        # 获取TARA数据
        ret = export_tara_report_to_word(target_path, current_user.project_id)
        if ret:
            # 返回文件
            return send_file(
                target_path,
                as_attachment=True,
                download_name=quote(target_filename.encode('utf-8')),
                mimetype='application/vnd.openxmlformats-officedocument.wordprocessingml.document'
            )
    except Exception as e:
        app.logger.error(f'导出TARA报告Word失败: {str(e)}')
        return jsonify({'success': False, 'message': f'导出失败: {str(e)}'}), 500
    finally:
        # 清理临时文件
        if 'target_path' in locals() and os.path.exists(target_path):
            try:
                os.remove(target_path)
            except Exception as e:
                app.logger.error(f'清理临时文件失败: {str(e)}')


@app.route('/export_tara_table')
@login_required
def export_tara_table():
    try:
        # 获取当前项目信息
        task = ProjectTable.query.get(current_user.project_id)
        export_path_with_uid = os.path.join(EXPORT_DIR, str(current_user.uid))
        if not os.path.exists(export_path_with_uid):
            os.makedirs(export_path_with_uid)

        # 生成目标文件名（包含项目简称和时间戳）
        timestamp = datetime.now().strftime("%Y%m%d_%H%M")
        target_filename = f'{task.abbr}_TARA报告_{timestamp}.xlsx'
        target_path = os.path.join(export_path_with_uid, target_filename)

        # 复制模板文件
        template_path = os.path.join(TEMPLATE_DIR, TEMPLATE_TARA)
        shutil.copy2(template_path, target_path)
        # TODO: 这里可以添加将数据写入Excel文件的逻辑
        ret = export_tara_table_to_excel(target_path,'',current_user.project_id)
        if not ret:
            return jsonify({'success': False, 'message': f'导出失败'}), 500
        # 返回文件
        return send_file(
            target_path,
            as_attachment=True,
            download_name=quote(target_filename.encode('utf-8')),
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        
    except Exception as e:
        app.logger.error(f'导出TARA报告失败: {str(e)}')
        return jsonify({'success': False, 'message': f'导出失败: {str(e)}'}), 500
    finally:
        # 清理临时文件
        if 'target_path' in locals() and os.path.exists(target_path):
            try:
                os.remove(target_path)
            except Exception as e:
                app.logger.error(f'清理临时文件失败: {str(e)}')

@app.route('/get_ecu_list', methods=['GET'])
@login_required
def get_ecu_list():
    try:
        # 获取当前用户项目下的所有Item
        ecus = EcuTable.query.filter_by(Project_id=current_user.project_id).all()
        
        # 转换为JSON格式
        ecu_list = [{
            'id': ecu.id,
            'name': ecu.abbr_ext,
        } for ecu in ecus]
        
        return jsonify({
            'code': 0,
            'msg': '成功',
            'count': len(ecu_list),
            'data': ecu_list
        })
        
    except Exception as e:
        app.logger.error(f'获取ECU列表失败: {str(e)}')
        return jsonify({
            'code': 1,
            'msg': f'获取失败: {str(e)}',
            'count': 0,
            'data': []
        })


if __name__ == '__main__':
    #app.run(port=5555, debug=True)
    #(.virtualenvs) PS D:\Gitee\tara-platform> python app.py
    serve(app, host='127.0.0.1', port=5555)

